34,575
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('student') IS NOT NULL DROP TABLE student
GO
CREATE TABLE student(
sno VARCHAR(20),
sname NVARCHAR(10)
)
GO
INSERT INTO student
SELECT 201215121,N'李勇'
UNION ALL SELECT 201215122,N'刘晨'
UNION ALL SELECT 201215123,N'王敏'
UNION ALL SELECT 201215126,N'杨天'
UNION ALL SELECT 201215125,N'张立'
GO
IF OBJECT_ID('course') IS NOT NULL DROP TABLE course
GO
CREATE TABLE course(
cno VARCHAR(20),
cname NVARCHAR(10)
)
GO
INSERT INTO course
SELECT 1,N'数据库'
UNION ALL SELECT 2,N'数学'
UNION ALL SELECT 3,N'信息系统'
UNION ALL SELECT 4,N'操作系统'
UNION ALL SELECT 5,N'数据结构'
UNION ALL SELECT 6,N'数据处理'
UNION ALL SELECT 7,N'PASCAL语言'
GO
IF OBJECT_ID('sc') IS NOT NULL DROP TABLE sc
GO
CREATE TABLE sc(
sno VARCHAR(20),
cno VARCHAR(20),
grade INT
)
GO
INSERT INTO sc
SELECT 201215121, 1, 92
UNION ALL SELECT 201215121, 2, 85
UNION ALL SELECT 201215121, 3, 88
UNION ALL SELECT 201215122, 2, 90
UNION ALL SELECT 201215122, 3, 80
UNION ALL SELECT 201215126, 1, 80
UNION ALL SELECT 201215126, 2, 70
UNION ALL SELECT 201215126, 3, 60
UNION ALL SELECT 201215126, 4, 85
UNION ALL SELECT 201215126, 5, 89
UNION ALL SELECT 201215126, 6, 90
UNION ALL SELECT 201215126, 7, 94
GO
--先查出内部子查询的结果
SELECT SC.sno
FROM sc
GROUP BY
SC.sno
HAVING COUNT(cno) != 7
/*
sno
201215121
201215122
*/
--改为等价写法
/*
原:
SELECT sname
FROM student
WHERE NOT EXISTS
(
SELECT SC.sno
FROM sc
WHERE Student.Sno = sc.sno
GROUP BY
SC.sno
HAVING COUNT(cno) != 7
)
*/
-- ==>
SELECT *
FROM student
LEFT JOIN
(
SELECT SC.sno
FROM sc
GROUP BY
SC.sno
HAVING COUNT(cno) != 7
) AS t ON student.sno=t.sno
--WHERE t.sno IS NULL --为便于观察,暂时不加 where
/*
sno sname sno
-------------------- ---------- --------------------
201215121 李勇 201215121
201215122 刘晨 201215122
201215123 王敏 NULL
201215126 杨天 NULL
201215125 张立 NULL
*/
这样看就非常清晰了吧。
你 #2 的, 写出完整的 sql , 半截运行不了。