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 ON10 GO11 --分数表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] ASC21 )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 GO25 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] OFF36 37 38 39 40 41 42 SELECT * FROM T_Scores AS T43 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 T49 ------------------------------------------50 SELECT T.SubjectName,T.GradeName, SUM(T.Score) AS Sum_Score51 FROM T_Scores AS T52 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