create table sc(SNO varchar(20), CNO varchar(20), 成绩 int) insert sc select '001', 'english', 80 union all select '001', 'chinese', 70 union all select '002', 'english', 75 union all select '002', 'chinese', 90 union all select '002', 'english1', 85 union all select '003', 'chinese', 80 union all select '004', 'english', 80 union all select '004', 'china', 80也可以为in,楼主要的结果用判断直假的方式效率好:select * from sc where sno in( select SNO from sc a where cno in(select cno from sc where SNO='001') group by SNO having count(1)=(select count(1) from sc where SNO='001'))--举例说明楼主的语句 SELECT * FROM sc A WHERE NOT EXISTS ( SELECT * FROM sc B WHERE B.SNO='001' AND NOT EXISTS (SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO=A.SNO) )not exists--条件为假时成立 扫描第一条记录时条件为:SELECT * FROM sc B WHERE B.SNO='001' AND NOT EXISTS (SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO='001')--为假成立,所有C.SNO=001的都成立 当C.SNO='002';SELECT * FROM sc B WHERE B.SNO='001' AND NOT EXISTS (SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO='002')--为假成立,所有C.SNO=002的都成立 当C.SNO='003';SELECT * FROM sc B WHERE B.SNO='001' AND NOT EXISTS (SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO='003')--有一条记录显示为真不成立 /*SNO CNO 成绩 -------------------- -------------------- ----------- 001 english 80(所影响的行数为 1 行)*/当C.SNO='004'; SELECT * FROM sc B WHERE B.SNO='001' AND NOT EXISTS (SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO='004')--有一条记录显示为真不成立 /* SNO CNO 成绩 -------------------- -------------------- ----------- 001 chinese 70(所影响的行数为 1 行)*/
SELECT * FROM SC A
WHERE
EXISTS
(SELECT 1 FROM SC C WHERE C.CNO=A.CNO AND C.SNO=95002 )
--去掉一个AND C.SNO=A.SNO
insert sc select '001', 'english', 80
union all select '001', 'chinese', 70
union all select '002', 'english', 75
union all select '002', 'chinese', 90
union all select '002', 'english1', 85
union all select '003', 'chinese', 80
union all select '004', 'english', 80
union all select '004', 'china', 80也可以为in,楼主要的结果用判断直假的方式效率好:select * from sc where sno in(
select SNO from sc a where cno in(select cno from sc where SNO='001')
group by SNO
having count(1)=(select count(1) from sc where SNO='001'))--举例说明楼主的语句
SELECT * FROM sc A
WHERE NOT EXISTS
(
SELECT * FROM sc B WHERE B.SNO='001'
AND
NOT EXISTS
(SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO=A.SNO)
)not exists--条件为假时成立
扫描第一条记录时条件为:SELECT * FROM sc B WHERE B.SNO='001'
AND
NOT EXISTS
(SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO='001')--为假成立,所有C.SNO=001的都成立
当C.SNO='002';SELECT * FROM sc B WHERE B.SNO='001'
AND
NOT EXISTS
(SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO='002')--为假成立,所有C.SNO=002的都成立
当C.SNO='003';SELECT * FROM sc B WHERE B.SNO='001'
AND
NOT EXISTS
(SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO='003')--有一条记录显示为真不成立
/*SNO CNO 成绩
-------------------- -------------------- -----------
001 english 80(所影响的行数为 1 行)*/当C.SNO='004';
SELECT * FROM sc B WHERE B.SNO='001'
AND
NOT EXISTS
(SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO='004')--有一条记录显示为真不成立
/*
SNO CNO 成绩
-------------------- -------------------- -----------
001 chinese 70(所影响的行数为 1 行)*/