三?表,要??出??了所有?程的学生的成?
Example:
std表
std_id std_name
0001 alex
0002 bill
0003 cliton
0004 duke
course表
course_id course_name
2001 english
2002 math
2003 physics
score表
std_id course_id score
0001 2001 81
0002 2001 91
0001 2003 24
0003 2002 62
0001 2002 54
?出:
alex english 81
alex math 54
alex physics 91
想了半天,没什?思路,估?会用到HAVING,IN,EXISTS之?,可是自己??几个??字的用法太不熟悉,借?个机会好好看看。
1.?出一??程都没有?的学生姓名,?个比???,用个EXISTS就?定
SELECT std_name,std_id FROM std
WHERE NOT EXISTS
(SELECT * FROM score
WHERE std_id=std.std_id)
想想EXISTS和IN有很多共同点,因此用IN??也能解决??
SELECT std_name,std_id FROM std
WHERE std_id NOT IN
(SELECT std_id FROM score)
2.?出?了所有?程的学生的姓名,?个就比?麻?了,居然要用?个NOT EXISTS
SELECT std_name
FROM std
WHERE NOT EXISTS
(SELECT *
FROM course
WHERE NOT EXISTS
(SELECT *
FROM score
WHERE (std_id=std.std_id AND course_id=course.course_id)
)
)
USE tempdb
GO
CREATE TABLE std
(std_id VARCHAR(4), std_name VARCHAR(100))
GO
CREATE TABLE course
(course_id VARCHAR(4), course_name VARCHAR(100))
GO
CREATE TABLE score
(std_id VARCHAR(4), course_id VARCHAR(4) , chengji INT)
GO
INSERT INTO std
SELECT '0001' , 'alex' UNION ALL
SELECT '0002' , 'bill' UNION ALL
SELECT '0003' , 'cliton' UNION ALL
SELECT '0004' , 'duke'
GO
INSERT INTO COURSE
SELECT '2001' , 'english' UNION ALL
SELECT '2002' , 'math' UNION ALL
SELECT '2003' , 'physics'
GO
INSERT INTO score
SELECT '0001' , '2001' , 81 UNION ALL
SELECT '0002' , '2001' , 91 UNION ALL
SELECT '0001' , '2003' , 24 UNION ALL
SELECT '0003' , '2002' , 62 UNION ALL
SELECT '0001' , '2002' , 54