--> 测试数据: @学生表S
declare @学生表S table (学号SID int,名称SN varchar(2))
insert into @学生表S
select 1,'王' union all
select 2,'李'

--> 测试数据: @课程表C
declare @课程表C table (编号CID int,课程名CN varchar(2))
insert into @课程表C
select 1,'C1' union all
select 2,'C2'

--> 测试数据: @成绩表SC
declare @成绩表SC table (学号SID int,课程编号CID int,成绩G int)
insert into @成绩表SC
select 1,1,100 union all
select 1,2,100 union all
select 1,1,90 union all
select 2,1,100 union all
select 2,2,90 union all
select 2,2,80

--1)查每一个学生最好成绩的课程名和成绩

select b.名称SN,c.课程名CN,max(成绩G) 
from (select * from @成绩表SC t
where 成绩G=
(select max(成绩G) from @成绩表SC where 学号SID=t.学号SID)) a ,@学生表S b,@课程表C c where a.学号SID=b.学号SID and a.课程编号CID=c.编号CID
group by b.名称SN,c.课程名CN
/*
名称SN 课程名CN 
---- ----- -----------
李    C1    100
王    C1    100
王    C2    100
*/
--2)查学生考过两次的课程名和成绩
select d.名称SN,c.课程名CN,a.成绩G from @成绩表SC a right join (
select 学号SID,课程编号CID from @成绩表SC group by 学号SID,课程编号CID having count(1)=2
) b on a.学号SID=b.学号SID and a.课程编号CID=b.课程编号CID
left join @课程表C c on a.课程编号CID=c.编号CID
left join @学生表S d on a.学号SID=d.学号SID
/*
名称SN 课程名CN 成绩G
---- ----- -----------
王    C1    100
王    C1    90
李    C2    90
李    C2    80
*/

------解决方案--------------------
s(sno int,sname varchar);
c(cno int,cname varchar);
sc(sno int,cno int,g int);
---------------------
select c.cname,d.sno from c join (select cno,sno from sc a join (select max(g) g,sno from sc) b on a.sno=b.sno and a.g=b.g) d on c.cno=d.cno  

-----
select c.name,d.g from c ,sc d,(
select  
sno,cno
from sc  
group by sno,cno  
having count(1)>=2) e
where c.cno=d.cno and d.cno=e.cno

------解决方案--------------------
SQL code

go
if object_id('TBL') is not null 
drop table TBL
go
create table TBL(
[SID] int,
[SN] varchar(2))
insert TBL
select 1,'王' union all
select 2,'李'
select *from TBL            
GO
if object_id('[C1]') is not null 
drop table [C1]
GO
create table [C1]([编号CID] int,[课程名CN] varchar(2))
GO
insert [C1]

select 1,'C1' union all
select 2,'C2'
         
--> 测试数据：[SC]
GO
if object_id('[SC]') is not null 
drop table [SC]
GO
create table [SC](
[学号SID] int,
[课程编号CID] int,
[成绩G] int)
GO
insert [SC]
select 1,1,100 union all
select 1,2,100 union all
select 1,1,90 union all
select 2,1,100 union all
select 2,2,90 union all
select 2,2,80


;with T
AS
(
SELECT *FROM TBL INNER JOIN(
 SELECT [SC].*,[C1].* FROM [SC] LEFT JOIN [C1] 
 ON [SC].[课程编号CID]=[C1].[编号CID])A
 ON TBL.[SID]=A.学号SID
)

SELECT SN,[课程名CN],[成绩G] FROM T
WHERE [成绩G]=(
SELECT MAX([成绩G])FROM [SC] WHERE T.[SID]=[SC].学号SID)

/*
SN    课程名CN    成绩G
李    C1    100
王    C1    100
王    C2    100
*/

/*
2)查学生考过两次的课程名和成绩
结果 王 C1 100
  王 C1 90
  李 C2 90
  李 C2 80
*/

;with T
AS
(
SELECT *FROM TBL INNER JOIN(
 SELECT [SC].*,[C1].* FROM [SC] LEFT JOIN [C1] 
 ON [SC].[课程编号CID]=[C1].[编号CID])A
 ON TBL.[SID]=A.学号SID
)


SELECT SN,[课程名CN],[成绩G] FROM T WHERE [课程名CN] IN(
SELECT [课程名CN] FROM (
SELECT SN,[课程名CN],COUNT(*) AS NUM FROM T GROUP BY SN,[课程名CN])A WHERE NU