27,579
社区成员
发帖
与我相关
我的任务
分享
select A.Name,Max(B.Grade) as Grade From Course A
inner join SC B ON B.CNO=A.CNO
Group by A.Name
USE tempdb
GO
IF OBJECT_ID('course') IS NOT NULL DROP TABLE course
IF OBJECT_ID('sc') IS NOT NULL DROP TABLE sc
CREATE TABLE course(
cno CHAR(3),
cname CHAR(20),
ccredit INT
)
CREATE TABLE sc(
sno CHAR(10),
cno CHAR(3),
grade INT
)
SET NOCOUNT ON
INSERT INTO course VALUES('001','数据库原理',4)
INSERT INTO course VALUES('002','C程序设计',2)
INSERT INTO course VALUES('003','软件工程',4)
INSERT INTO sc VALUES ('2009010001','001',80)
INSERT INTO sc VALUES ('2009010001','002',75)
INSERT INTO sc VALUES ('2009010001','003',65)
INSERT INTO sc VALUES ('2009010002','001',70)
INSERT INTO sc VALUES ('2009020001','002',75)
INSERT INTO sc VALUES ('2009020002','001',56)
INSERT INTO sc VALUES ('2009020002','003',68)
SELECT
(SELECT c.cname FROM course AS c WHERE c.cno=sc.cno) AS [课程名]
,MAX(grade) AS [最高分]
FROM sc GROUP BY cno
/*
课程名 最高分
-------------------- -----------
数据库原理 80
C程序设计 75
软件工程 68
*/
Select cname,max(grade)
From (
Select cname,grade
From sc a Join course b on a.cno=b.cno
) a
Group By cname