group by 用法？解决思路

if object_id('[TBa]') is not null drop table [TBa]gocreate table [TBa] (id int,班级 nvarchar(4))insert into [TBa]select 1,'一班' union allselect 2,'二班' union allselect 3,'三班'if object_id('[TBb]') is not null drop table [TBb]gocreate table [TBb] (id int,name nvarchar(4),b_id int)insert into [TBb]select 1,'aa',1 union allselect 2,'bb',1 union allselect 3,'cc',2 union allselect 4,'dd',3 union allselect 5,'ff',3 union allselect 6,'gg',3if object_id('[TBc]') is not null drop table [TBc]gocreate table [TBc] (id int,语文 int,数学 int,s_id int)insert into [TBc]select 1,11,22,6 union allselect 2,33,44,5 union allselect 3,55,66,4 union allselect 4,77,88,3 union allselect 5,99,12,2 union allselect 6,24,100,1select * from [TBa]select * from [TBb]select * from [TBc]WITH ttAS(SELECT a.班级,b.NAME,c.数学FROM TBc cINNER JOIN TBb b ON c.s_id = b.idINNER JOIN TBa a ON a.id =b.b_id)SELECT a.*FROM tt aWHERE NOT EXISTS(SELECT 1 FROM tt WHERE a.班级 = tt.班级 AND a.数学 <tt.数学)ORDER BY a.班级/*班级    NAME    数学二班    cc    88三班    dd    66一班    aa    100*/
------解决方案--------------------
SQL code
WITH ttAS(SELECT a.班级,b.NAME,c.数学,c.语文FROM TBc cINNER JOIN TBb b ON c.s_id = b.idINNER JOIN TBa a ON a.id =b.b_id)SELECT a.班级,a.NAME AS '数学高分者',a.数学,b.NAME AS '语文高分者',b.语文  FROM (SELECT a.班级,a.NAME,a.数学FROM tt aWHERE NOT EXISTS(SELECT 1 FROM tt WHERE a.班级 = tt.班级 AND a.数学 <tt.数学)) aINNER JOIN (SELECT a.班级,a.NAME,a.语文FROM tt aWHERE NOT EXISTS(SELECT 1 FROM tt WHERE a.班级 = tt.班级 AND a.语文 <tt.语文)) B ON a.班级 =b.班级/*班级    数学高分者    数学    语文高分者    语文一班    aa    100    bb    99二班    cc    88    cc    77三班    dd    66    dd    55*/
------解决方案--------------------
SQL code
Declare @A Table (ID int, Name Varchar(20))Insert Into @A Select 1, '一班'Union All Select 2, '二班'Union All Select 3, '三班'Declare @B Table (ID Int, Name Varchar(20), B_ID Int)Insert Into @B (ID, Name, B_ID)Select 1, 'AA', 1Union All Select 2, 'BB', 1Union All Select 3, 'CC', 2Union All Select 4, 'DD', 3Union All Select 5, 'FF', 3Union All Select 6, 'GG', 3Declare @C Table (ID Int, Chinese Int, Math Int, S_Id Int)Insert Into @CSelect 1, 11, 22, 6Union ALL Select 2, 33, 44, 5Union ALL Select 3, 55, 66, 4Union ALL Select 4, 77, 88, 3Union ALL Select 5, 99, 12, 2Union ALL Select 6, 24, 100, 1--问题一：求各班数学成绩最好的同学 （展示表字段 ： 班级 同学名字 数学成绩）Select A.Name ClassName, B.Name StuName, C.Math From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.IDAnd Exists (Select Name, MaxMath From (                 Select A.Name, MAX(C.Math) MaxMath From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.ID Group By A.Name) D                 Where A.Name=D.Name And C.Math=D.MaxMath              )/*ClassName            StuName              Math-------------------- -------------------- -----------一班                   AA                   100二班                   CC                   88三班                   DD                   66*/--求各班语文成绩最好的同学  Select A.Name ClassName, B.Name StuName, C.Chinese From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.IDAnd Exists (Select Name, MaxChinese From (                 Select A.Name, MAX(C.Chinese) MaxChinese From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.ID Group By A.Name) D                 Where A.Name=D.Name And C.Chinese=D.MaxChinese              )/*ClassName            StuName              Chinese-------------------- -------------------- -----------一班                   BB                   99二班                   CC                   77三班                   DD                   55*/              --问题二：求各班数学成绩最好的同学、各班语文成绩最好的同学 Select T_Chinese.ClassName, T_Chinese.StuName, T_Chinese.Chinese, T_Math.ClassName, T_Math.Math From(Select A.Name ClassName, B.Name StuName, C.Chinese From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.IDAnd Exists (Select Name, MaxChinese From (                 Select A.Name, MAX(C.Chinese) MaxChinese From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.ID Group By A.Name) D                 Where A.Name=D.Name And C.Chinese=D.MaxChinese              ))As T_Chinese ,(Select A.Name ClassName, B.Name StuName, C.Math From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.IDAnd Exists (Select Name, MaxMath From (                 Select A.Name, MAX(C.Math) MaxMath From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.ID Group By A.Name) D                 Where A.Name=D.Name And C.Math=D.MaxMath              )) As T_Math Where T_Chinese.ClassName=T_Math.ClassName/*ClassName            StuName              Chinese     ClassName            Math-------------------- -------------------- ----------- -------------------- -----------一班                   BB                   99          一班                   100三班                   DD                   55          三班                   66二班                   CC                   77          二班                   88*/