if object_id('[TBa]') is not null drop table [TBa]
go
create table [TBa] (id int,班级 nvarchar(4))
insert into [TBa]
select 1,'一班' union all
select 2,'二班' union all
select 3,'三班'

if object_id('[TBb]') is not null drop table [TBb]
go
create table [TBb] (id int,name nvarchar(4),b_id int)
insert into [TBb]
select 1,'aa',1 union all
select 2,'bb',1 union all
select 3,'cc',2 union all
select 4,'dd',3 union all
select 5,'ff',3 union all
select 6,'gg',3

if object_id('[TBc]') is not null drop table [TBc]
go
create table [TBc] (id int,语文 int,数学 int,s_id int)
insert into [TBc]
select 1,11,22,6 union all
select 2,33,44,5 union all
select 3,55,66,4 union all
select 4,77,88,3 union all
select 5,99,12,2 union all
select 6,24,100,1

select * from [TBa]
select * from [TBb]
select * from [TBc]

WITH tt
AS(
SELECT a.班级,b.NAME,c.数学
FROM TBc c
INNER JOIN TBb b ON c.s_id = b.id
INNER JOIN TBa a ON a.id =b.b_id)

SELECT a.*
FROM tt a
WHERE 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 tt
AS(
SELECT a.班级,b.NAME,c.数学,c.语文
FROM TBc c
INNER JOIN TBb b ON c.s_id = b.id
INNER 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 a
WHERE NOT EXISTS(SELECT 1 FROM tt WHERE a.班级 = tt.班级 AND a.数学 <tt.数学)
) a
INNER JOIN 
(SELECT a.班级,a.NAME,a.语文
FROM tt a
WHERE 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', 1
Union All Select 2, 'BB', 1
Union All Select 3, 'CC', 2
Union All Select 4, 'DD', 3
Union All Select 5, 'FF', 3
Union All Select 6, 'GG', 3

Declare @C Table (ID Int, Chinese Int, Math Int, S_Id Int)
Insert Into @C
Select 1, 11, 22, 6
Union ALL Select 2, 33, 44, 5
Union ALL Select 3, 55, 66, 4
Union ALL Select 4, 77, 88, 3
Union ALL Select 5, 99, 12, 2
Union 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.ID
And 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 Cl