请教个SQL语句的写法

[taick]

学生表S 学号SID 名称SN
1 王
2 李

课程表C 编号CID 课程名CN
1 C1
2 C2

成绩表SC 学号SID 课程编号CID 成绩G
1 1 100
1 2 100
1 1 90
2 1 100
2 2 90
2 2 80

1)查每一个学生最好成绩的课程名和成绩
结果 王 C1 100
王 C2 100
李 C1 100

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

请各位大大指教下SQL语句的写法。

[叶子]

--> 测试数据: @学生表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
*/

[勿勿]

自己去关联查询吧

[tomyuansir]

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

[开着拖拉机泡妞]

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 NUM=2 AND A.SN=T.SN)
/*
SN	课程名CN	成绩G
王	C1	100
王	C1	90
李	C2	90
李	C2	80
*/

[孤独加百列]

/*学生表S 学号SID 名称SN
  1 王
  2 李
*/
CREATE TABLE S
(
	SID INT NOT NULL,
	SN VARCHAR(100) NOT NULL
)

INSERT INTO S
SELECT 1,'王' UNION 
SELECT 2,'李'
/*
课程表C 编号CID 课程名CN
  1 C1
  2 C2
*/
CREATE TABLE C
(
	CID INT NOT NULL,
	CN VARCHAR(100) NOT NULL
)
INSERT INTO C
SELECT 1,'C1' UNION 
SELECT 2,'C2'

/*
成绩表SC 学号SID 课程编号CID 成绩G
  1 1 100
  1 2 100
  1 1 90
  2 1 100
  2 2 90
  2 2 80
*/
CREATE TABLE SC
(
	SID INT NOT NULL,
	CID INT NOT NULL,
	G INT NOT NULL
)
INSERT INTO SC
select  1, 1, 100 UNION
SELECT  1, 2, 100 UNION
SELECT  1, 1, 90 UNION
SELECT  2, 1, 100 UNION
SELECT  2, 2, 90 UNION
SELECT  2, 2, 80 


/*1)查每一个学生最好成绩的课程名和成绩
结果 王 C1 100
  王 C2 100
  李 C1 100
*/

SELECT SN,CN,G
FROM S,C,SC,(	SELECT SID,MAX(G) AS grade
				FROM SC 
				GROUP BY SID) AS A
WHERE S.SID = SC.SID AND C.CID = SC.CID AND SC.SID = A.SID AND SC.G = A.grade


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

SELECT SN,CN,G
FROM S,C,SC,(	SELECT SID,CID,COUNT(G) AS Num
				FROM SC
				GROUP BY SID,CID
				HAVING COUNT(G) = 2) AS A
WHERE S.SID = A.SID AND SC.SID = A.SID AND C.CID = A.CID AND A.CID = SC.CID