请教个SQL语句的写法
学生表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语句的写法。
[解决办法]
- SQL code
--> 测试数据: @学生表Sdeclare @学生表S table (学号SID int,名称SN varchar(2))insert into @学生表Sselect 1,'王' union allselect 2,'李'--> 测试数据: @课程表Cdeclare @课程表C table (编号CID int,课程名CN varchar(2))insert into @课程表Cselect 1,'C1' union allselect 2,'C2'--> 测试数据: @成绩表SCdeclare @成绩表SC table (学号SID int,课程编号CID int,成绩G int)insert into @成绩表SCselect 1,1,100 union allselect 1,2,100 union allselect 1,1,90 union allselect 2,1,100 union allselect 2,2,90 union allselect 2,2,80--1)查每一个学生最好成绩的课程名和成绩select b.名称SN,c.课程名CN,max(成绩G) from (select * from @成绩表SC twhere 成绩G=(select max(成绩G) from @成绩表SC where 学号SID=t.学号SID)) a ,@学生表S b,@课程表C c where a.学号SID=b.学号SID and a.课程编号CID=c.编号CIDgroup 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.课程编号CIDleft join @课程表C c on a.课程编号CID=c.编号CIDleft 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
goif object_id('TBL') is not null drop table TBLgocreate table TBL([SID] int,[SN] varchar(2))insert TBLselect 1,'王' union allselect 2,'李'select *from TBL GOif object_id('[C1]') is not null drop table [C1]GOcreate table [C1]([编号CID] int,[课程名CN] varchar(2))GOinsert [C1]select 1,'C1' union allselect 2,'C2' --> 测试数据：[SC]GOif object_id('[SC]') is not null drop table [SC]GOcreate table [SC]([学号SID] int,[课程编号CID] int,[成绩G] int)GOinsert [SC]select 1,1,100 union allselect 1,2,100 union allselect 1,1,90 union allselect 2,1,100 union allselect 2,2,90 union allselect 2,2,80;with TAS(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 TWHERE [成绩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 TAS(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*/
[解决办法]
- SQL code
/*学生表S 学号SID 名称SN 1 王 2 李*/CREATE TABLE S( SID INT NOT NULL, SN VARCHAR(100) NOT NULL)INSERT INTO SSELECT 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 CSELECT 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 SCselect 1, 1, 100 UNIONSELECT 1, 2, 100 UNIONSELECT 1, 1, 90 UNIONSELECT 2, 1, 100 UNIONSELECT 2, 2, 90 UNIONSELECT 2, 2, 80 /*1)查每一个学生最好成绩的课程名和成绩结果 王 C1 100 王 C2 100 李 C1 100*/SELECT SN,CN,GFROM S,C,SC,( SELECT SID,MAX(G) AS grade FROM SC GROUP BY SID) AS AWHERE 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,GFROM S,C,SC,( SELECT SID,CID,COUNT(G) AS Num FROM SC GROUP BY SID,CID HAVING COUNT(G) = 2) AS AWHERE S.SID = A.SID AND SC.SID = A.SID AND C.CID = A.CID AND A.CID = SC.CID