关于分组统计注意事项 

 1 --关于分组统计注意事项。
 2 --注：本文不讨论数据库表设计，故不要把精力放在关于表设计是否合理的问题上。  
 3 
 4 USE [Cest_TestDB]
 5 GO
 6 /****** Object:  Table [dbo].[T_Scores]    Script Date: 2014/8/28 9:47:21 ******/
 7 SET ANSI_NULLS ON
 8 GO
 9 SET QUOTED_IDENTIFIER ON
10 GO
11 --分数表
12 CREATE TABLE [dbo].[T_Scores](
13     [ID] [int] IDENTITY(1,1) NOT NULL,
14     [SubjectName] [nvarchar](50) NULL, --学科名称 不唯一
15     [GradeName] [nvarchar](50) NULL, -- 年级名称 不唯一
16     [Score] [decimal](18, 2) NULL, -- 分数
17     [StudentName] [nvarchar](50) NULL, -- 学生姓名 不唯一
18  CONSTRAINT [PK_T_Scores] PRIMARY KEY CLUSTERED 
19 (
20     [ID] ASC
21 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
22 ) ON [PRIMARY]
23 
24 GO
25 SET IDENTITY_INSERT [dbo].[T_Scores] ON 
26 -- 测试数据
27 INSERT [dbo].[T_Scores] ([ID], [SubjectName], [GradeName], [Score], [StudentName]) VALUES (1, N'语文', N'一年级', CAST(100.00 AS Decimal(18, 2)), N'小白')
28 INSERT [dbo].[T_Scores] ([ID], [SubjectName], [GradeName], [Score], [StudentName]) VALUES (2, N'数学', N'一年级', CAST(90.00 AS Decimal(18, 2)), N'小白')
29 INSERT [dbo].[T_Scores] ([ID], [SubjectName], [GradeName], [Score], [StudentName]) VALUES (3, N'语文', N'二年级', CAST(100.00 AS Decimal(18, 2)), N'小苹果')
30 INSERT [dbo].[T_Scores] ([ID], [SubjectName], [GradeName], [Score], [StudentName]) VALUES (4, N'数学', N'二年级', CAST(90.00 AS Decimal(18, 2)), N'小苹果')
31 INSERT [dbo].[T_Scores] ([ID], [SubjectName], [GradeName], [Score], [StudentName]) VALUES (5, N'英语', N'三年级', CAST(60.00 AS Decimal(18, 2)), N'萌萌哒')
32 INSERT [dbo].[T_Scores] ([ID], [SubjectName], [GradeName], [Score], [StudentName]) VALUES (8, N'语文', N'一年级', CAST(100.00 AS Decimal(18, 2)), N'小黑黑')
33 INSERT [dbo].[T_Scores] ([ID], [SubjectName], [GradeName], [Score], [StudentName]) VALUES (9, N'语文', N'一年级', CAST(100.00 AS Decimal(18, 2)), N'小白白')
34 INSERT [dbo].[T_Scores] ([ID], [SubjectName], [GradeName], [Score], [StudentName]) VALUES (10, N'数学', N'一年级', CAST(90.00 AS Decimal(18, 2)), N'小黑黑')
35 SET IDENTITY_INSERT [dbo].[T_Scores] OFF
36 
37 
38 
39 
40 
41 
42 SELECT * FROM T_Scores AS T
43 
44 SELECT DISTINCT SubjectName FROM T_Scores  --SubjectName只保留一个
45 SELECT DISTINCT SubjectName,GradeName FROM T_Scores --SubjectName,GradeName组合唯一
46 
47 --统计各学科各年级分数
48 SELECT * FROM T_Scores AS T
49 ------------------------------------------
50 SELECT T.SubjectName,T.GradeName, SUM(T.Score) AS Sum_Score
51 FROM T_Scores AS T
52 LEFT OUTER JOIN ( 
53     SELECT DISTINCT SubjectName,GradeName FROM T_Scores)
54 AS B ON T.SubjectName = B.SubjectName AND T.GradeName = B.GradeName --分组统计注意组合条件不能少！！，否则统计结果不正确
55 GROUP BY T.SubjectName, T.GradeName

 

posted @ 2014-08-28 10:10  五月雨恋  阅读(902)  评论(0)    收藏  举报