[求助]一个相对复杂的查询语句问题

Question

现有两张表
create table C(C# int,CN varchar)
create table SC(S# int,C# int,G int)表中数据如下
C表
C#      CN
1 厚黑学
2 查询基础
3 能说
4 会道
SC表
S#      C#      G
1 1 1
1 3 6
2 1 75
2 2 55
2 3 7
2 4 7
3 3 77
4 1 75
4 2 55
4 3 7
4 4 7
6 6 6
7 4 7要求显示出SC表中的C#包含C表中所有的C#的行即结果是
2 1 75
2 2 55
2 3 7
2 4 7
4 1 75
4 2 55
4 3 7
4 4 7

Answer 1

select sc.* from sc where S# in (select S# from sc group by S# having count(*) = (select count(*) from C)) 

Answer 2

create table C(C# int, CN varchar(10))
insert into C values(1, '厚黑学') 
insert into C values(2, '查询基础') 
insert into C values(3, '能说') 
insert into C values(4, '会道') 
create table SC(S# int, C# int, G int)
insert into SC values(1 ,1 ,1 )
insert into SC values(1 ,3 ,6 )
insert into SC values(2 ,1 ,75) 
insert into SC values(2 ,2 ,55) 
insert into SC values(2 ,3 ,7 )
insert into SC values(2 ,4 ,7 )
insert into SC values(3 ,3 ,77) 
insert into SC values(4 ,1 ,75) 
insert into SC values(4 ,2 ,55) 
insert into SC values(4 ,3 ,7 )
insert into SC values(4 ,4 ,7 )
insert into SC values(6 ,6 ,6 )
insert into SC values(7 ,4 ,7 )
goselect sc.* from sc where S# in (select S# from sc group by S# having count(*) = (select count(*) from C)) drop table C,SC/*
S#          C#          G           
----------- ----------- ----------- 
2           1           75
2           2           55
2           3           7
2           4           7
4           1           75
4           2           55
4           3           7
4           4           7（所影响的行数为 8 行）
*/

Answer 3

create table C(C# int, CN varchar(10))
insert into C values(1, '厚黑学') 
insert into C values(2, '查询基础') 
insert into C values(3, '能说') 
insert into C values(4, '会道') 
create table SC(S# int, C# int, G int)
insert into SC values(1 ,1 ,1 )
insert into SC values(1 ,3 ,6 )
insert into SC values(2 ,1 ,75) 
insert into SC values(2 ,2 ,55) 
insert into SC values(2 ,3 ,7 )
insert into SC values(2 ,4 ,7 )
insert into SC values(3 ,3 ,77) 
insert into SC values(4 ,1 ,75) 
insert into SC values(4 ,2 ,55) 
insert into SC values(4 ,3 ,7 )
insert into SC values(4 ,4 ,7 )
insert into SC values(6 ,6 ,6 )
insert into SC values(7 ,4 ,7 )
goselect sc.* from sc where S# in (select S# from sc group by S# having count(*) = (select count(*) from C)) drop table C,SC/*
S#          C#          G           
----------- ----------- ----------- 
2           1           75
2           2           55
2           3           7
2           4           7
4           1           75
4           2           55
4           3           7
4           4           7（所影响的行数为 8 行）
*/

Answer 4

--2楼是我投机取巧利用总数获取的,这个是逐一比较得到的.
create table C(C# int, CN varchar(10))
insert into C values(1, '厚黑学') 
insert into C values(2, '查询基础') 
insert into C values(3, '能说') 
insert into C values(4, '会道') 
create table SC(S# int, C# int, G int)
insert into SC values(1 ,1 ,1 )
insert into SC values(1 ,3 ,6 )
insert into SC values(2 ,1 ,75) 
insert into SC values(2 ,2 ,55) 
insert into SC values(2 ,3 ,7 )
insert into SC values(2 ,4 ,7 )
insert into SC values(3 ,3 ,77) 
insert into SC values(4 ,1 ,75) 
insert into SC values(4 ,2 ,55) 
insert into SC values(4 ,3 ,7 )
insert into SC values(4 ,4 ,7 )
insert into SC values(6 ,6 ,6 )
insert into SC values(7 ,4 ,7 )
goselect sc.* from sc where S# not in
(select distinct t.S# from (select M.* , C.C# from (select distinct S# from SC) M,C) t where not exists (select 1 from SC n where n.S# = t.S# and n.C# = t.C#))drop table C,SC/*
S#          C#          G           
----------- ----------- ----------- 
2           1           75
2           2           55
2           3           7
2           4           7
4           1           75
4           2           55
4           3           7
4           4           7（所影响的行数为 8 行）
*/

Answer 5

啊!真对不起,一楼也没有错,这里不能删帖还能是相当的麻烦我之前说没有试出来的原因是因为我多插了一行insert into SC values(2 ,5 ,75) 得到的结果就只有S#          C#          G           
----------- ----------- ----------- 
4           1           75
4           2           55
4           3           7
4           4           7
请问在这种情况下
还要显示成
S#          C#          G           
----------- ----------- ----------- 
2           1           75
2           2           55
2           3           7
2           4           7
4           1           75
4           2           55
4           3           7
4           4           7
该如何编写,谢谢啦

Answer 6

select * from sc where c# in (select c# from c)

Answer 7

假使SC表中是买主资料而C表中是书的资料
您这种写法实际上表示的是显示买了C表中存在的书的清单

Answer 8

6楼给出的查询有5层结构一是select distinct S# from SC 即(取出SC表中唯一的S#值)代号为M
执行结果为
S#
1
2
3
4
6
7二是select M.* , C.C# from (select distinct S# from SC) M,C [(取出SC表中唯一的S#值)与C表中的C#值一起取出]代号为t
S#      C#
1 1
1 2
1 3
1 4
2 1
2 2
2 3
2 4
3 1
3 2
3 3
3 4
4 1
4 2
4 3
4 4
6 1
6 2
6 3
6 4
7 1
7 2
7 3
7 4但是在第三层select 1 from SC n where n.S# = t.S# and n.C# = t.C#上我有点卡住了这个select 1什么意思?

Answer 9

not exists (select 1 ....)表示不存在(只有有不存在的...)

Answer 10

就是把这个表(n)
S#      C# 
1 1 
1 2 
1 3 
1 4 
2 1 
2 2 
2 3 
2 4 
3 1 
3 2 
3 3 
3 4 
4 1 
4 2 
4 3 
4 4 
6 1 
6 2 
6 3 
6 4 
7 1 
7 2 
7 3 
7 4 和你的sc表
按n.S# = t.S# and n.C# = t.C#一一比较,不存在的选出来.

Answer 11

--一一加上个注释,这样你就明白了.
create table C(C# int, CN varchar(10))
insert into C values(1, '厚黑学') 
insert into C values(2, '查询基础') 
insert into C values(3, '能说') 
insert into C values(4, '会道') 
create table SC(S# int, C# int, G int)
insert into SC values(1 ,1 ,1 )
insert into SC values(1 ,3 ,6 )
insert into SC values(2 ,1 ,75) 
insert into SC values(2 ,2 ,55) 
insert into SC values(2 ,3 ,7 )
insert into SC values(2 ,4 ,7 )
insert into SC values(3 ,3 ,77) 
insert into SC values(4 ,1 ,75) 
insert into SC values(4 ,2 ,55) 
insert into SC values(4 ,3 ,7 )
insert into SC values(4 ,4 ,7 )
insert into SC values(6 ,6 ,6 )
insert into SC values(7 ,4 ,7 )
go
--1、取出所有的S#
select distinct S# from SC
/*
S#          
----------- 
1
2
3
4
6
7
（所影响的行数为 6 行）
*/--2、取出所有的S#,C#
select M.* , C.C# from (select distinct S# from SC) M,C
/*
S#          C#          
----------- ----------- 
1           1
1           2
1           3
1           4
2           1
2           2
2           3
2           4
3           1
3           2
3           3
3           4
4           1
4           2
4           3
4           4
6           1
6           2
6           3
6           4
7           1
7           2
7           3
7           4
（所影响的行数为 24 行）
*/--3、按上表找出在sc表不存在的S#,C#,然后只对S#取唯一即可。C#没用了。
select distinct t.S# from (select M.* , C.C# from (select distinct S# from SC) M,C) t where not exists (select 1 from SC n where n.S# = t.S# and n.C# = t.C#)
/*
S#          
----------- 
1
3
6
7
（所影响的行数为 4 行）
*/--4、从sc表里的S#找出不在上表S#就是你要的结果
select sc.* from sc where S# not in (select distinct t.S# from (select M.* , C.C# from (select distinct S# from SC) M,C) t where not exists (select 1 from SC n where n.S# = t.S# and n.C# = t.C#))drop table C,SC/*
S#          C#          G           
----------- ----------- ----------- 
2           1           75
2           2           55
2           3           7
2           4           7
4           1           75
4           2           55
4           3           7
4           4           7（所影响的行数为 8 行）
*/

Answer 12

select * from sc aa where not exists
(select 1 from  c a left join (select * from sc where s#=aa.s#) b on a.c#=b.c# 
where  b.c# is null)

Answer 13

create table C(C# int, CN varchar(10))
insert into C values(1, '厚黑学') 
insert into C values(2, '查询基础') 
insert into C values(3, '能说') 
insert into C values(4, '会道') 
create table SC(S# int, C# int, G int)
insert into SC values(1 ,1 ,1 )
insert into SC values(1 ,3 ,6 )
insert into SC values(2 ,1 ,75) 
insert into SC values(2 ,2 ,55) 
insert into SC values(2 ,3 ,7 )
insert into SC values(2 ,4 ,7 )
insert into SC values(3 ,3 ,77) 
insert into SC values(4 ,1 ,75) 
insert into SC values(4 ,2 ,55) 
insert into SC values(4 ,3 ,7 )
insert into SC values(4 ,4 ,7 )
insert into SC values(6 ,6 ,6 )
insert into SC values(7 ,4 ,7 )
go
select * from sc aa where not exists
(select 1 from  c a left join (select * from sc where s#=aa.s#) b on a.c#=b.c# 
where  b.c# is null)
--如果不需要其它多余记录，就加上下面的。
--and c# in(select c# from c)
/*
S#          C#          G           
----------- ----------- ----------- 
2           1           75
2           2           55
2           3           7
2           4           7
4           1           75
4           2           55
4           3           7
4           4           7
*/
drop table c,sc

Answer 14

如果把以上程序改为:select top 2 * from sc aa where not exists
(select  from  c a left join (select * from sc where s#=aa.s#) b on a.c#=b.c# 
where  b.c# is null)
会出如下的错..（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
服务器: 消息 156，级别 15，状态 1，行 2
在关键字 'from' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: 'b' 附近有语法错误。
共运行了17行正确.我想知道,上面的好句话,跑的先后顺序是什么..谢谢..

Answer 15

17 是插入例子数据产生的，另外你的语句漏了个1，在我这不会出错。
select top 2 * from sc aa where not exists
(select 就这少个 1
from  c a left join (select * from sc where s#=aa.s#) b on a.c#=b.c# 
where  b.c# is null)

Answer 16

if object_id('c') is not null
drop table c
if object_id('sc') is not null
drop table sc
go
create table C(C# int,CN varchar(20)) 
create table SC(S# int,C# int,G int) 
go
insert c
select 1,'厚黑學' union all
select 2,'查詢基礎' union all
select 3,'能說' union all
select 4,'會道'insert sc
select 1, 1, 1 union all 
select 1, 3, 6  union all
select 2, 1, 75  union all
select 2, 2, 55  union all
select 2, 3, 7  union all
select 2, 4, 7  union all
select 3, 3, 77  union all
select 4, 1, 75  union all
select 4, 2, 55  union all
select 4, 3, 7  union all
select 4, 4, 7  union all
select 6, 1, 8  union all
select 6, 6, 6  union all
select 7, 4, 7  union all
select 2, 5, 75go
select * from c
/*
C#  CN
1 厚黑學
2 查詢基礎
3 能說
4 會道
*/
select * from sc
/*
S# c# G
1 1 1
1 3 6
2 1 75
2 2 55
2 3 7
2 4 7
3 3 77
4 1 75
4 2 55
4 3 7
4 4 7
6 1 8
6 6 6
7 4 7
2 5 75
*/select * from sc aa where not exists
(
select * from c a where not exists(select 1 from sc where a.c#=c# and aa.s#=s#)
)
/*
S# c# G
2 1 75
2 2 55
2 3 7
2 4 7
4 1 75
4 2 55
4 3 7
4 4 7
2 5 75
*/select sc.* from sc where S# not in
(select distinct t.S# from (select M.* , C.C# from (select distinct S# from SC) M,C) t where not exists (select 1 from SC n where n.S# = t.S# and n.C# = t.C#))
/*
S# c# G
2 1 75
2 2 55
2 3 7
2 4 7
4 1 75
4 2 55
4 3 7
4 4 7
2 5 75
*/select * from sc aa where not exists
(select 1 from  c a left join (select * from sc where s#=aa.s#) b on a.c#=b.c# 
where  b.c# is null)
/*
S# c# G
2 1 75
2 2 55
2 3 7
2 4 7
4 1 75
4 2 55
4 3 7
4 4 7
2 5 75
*/

Answer 17

if object_id('c') is not null
drop table c
if object_id('sc') is not null
drop table sc
go
create table C(C# int,CN varchar(20)) 
create table SC(S# int,C# int,G int) 
go
insert c
select 1,'厚黑學' union all
select 2,'查詢基礎' union all
select 3,'能說' union all
select 4,'會道'insert sc
select 1, 1, 1 union all 
select 1, 3, 6  union all
select 2, 1, 75  union all
select 2, 2, 55  union all
select 2, 3, 7  union all
select 2, 4, 7  union all
select 3, 3, 77  union all
select 4, 1, 75  union all
select 4, 2, 55  union all
select 4, 3, 7  union all
select 4, 4, 7  union all
select 6, 1, 8  union all
select 6, 6, 6  union all
select 6, 3, 7  union all
select 6, 4, 7  union all
select 7, 4, 7  union all
select 2, 5, 75go
select * from c
/*
C#  CN
1 厚黑學
2 查詢基礎
3 能說
4 會道
*/
select * from sc
/*
S# c# G
1 1 1
1 3 6
2 1 75
2 2 55
2 3 7
2 4 7
3 3 77
4 1 75
4 2 55
4 3 7
4 4 7
6 1 8
6 6 6
6 3 7
6 4 7
7 4 7
2 5 75
*/--方法一
select * from sc aa where not exists
(
select * from c a where not exists(select 1 from sc where a.c#=c# and aa.s#=s#)
)
/*
S# c# G
2 1 75
2 2 55
2 3 7
2 4 7
4 1 75
4 2 55
4 3 7
4 4 7
2 5 75
*/--方法二
select * from sc a where exists
(
  select * from sc where C# in(select C# from c) group by S# having count(distinct C#)>=(select count(distinct C#) from c) and a.S#=S#
)
/*
S# c# G
2 1 75
2 2 55
2 3 7
2 4 7
4 1 75
4 2 55
4 3 7
4 4 7
2 5 75
*/--方法三
select sc.* from sc where S# not in
(select distinct t.S# from (select M.* , C.C# from (select distinct S# from SC) M,C) t where not exists (select 1 from SC n where n.S# = t.S# and n.C# = t.C#))
/*
S# c# G
2 1 75
2 2 55
2 3 7
2 4 7
4 1 75
4 2 55
4 3 7
4 4 7
2 5 75
*/--方法四
select * from sc aa where not exists
(select 1 from  c a left join (select * from sc where s#=aa.s#) b on a.c#=b.c# 
where  b.c# is null)
/*
S# c# G
2 1 75
2 2 55
2 3 7
2 4 7
4 1 75
4 2 55
4 3 7
4 4 7
2 5 75
*/

Answer 18

select sc.* from sc where S# in (select S# from sc group by S# having count(*) = (select count(*) from C)) 

Answer 19

话说对这个方法有个疑问是
select * from sc aa where not exists
(select 1 from  c a left join (select * from sc where s#=aa.s#) b on a.c#=b.c# 
where  b.c# is null)
中的select * from sc where s#=aa.s#不就是直接找SC表吗?能麻烦解释下吗?

Answer 20

select sc.* from sc where S# in (select S# from sc group by S# having count(*) = (select count(*) from C)) 
答案简洁明了。 

Answer 21

--包含所有c中书的s#号
select * from sc a
where (select count(distinct sc.c#)  from sc,c where sc.c#=c.c# and sc.s#=a.s#)=(select count(*) from c)

Answer 22

--包含所有c中书的s#号,可能会包含其它书号,再加上一条件,可排除它
select * from sc a
where (select count(distinct sc.c#)  from sc,c where sc.c#=c.c# and sc.s#=a.s#)=(select count(*) from c)
    and not exists(select * from sc where sc.s#=a.s# and c# not in (select c# from c))

Answer 23

简简单单
select * from sc where c# in (select distinct c# from c group by c#)

Answer 24

这位兄台的做法与前几楼的那几位一个思路,只是我往SC表中插入条C表中没有C#的数据就找不到这条记录,不能算正确

Answer 25

select * from sc a
where not exists (select 1 from c where not exists (select 1 from sc b where b.s#=a.s# and b.c#=c.c#))
测试如下：
Table dropped.
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.        S#         C#          G
---------- ---------- ----------
         2          1         75
         2          2         55
         2          3          7
         2          4          7
         4          1         75
         4          2         55
         4          3          7
         4          4          78 rows selected.
1 row created.        S#         C#          G
---------- ---------- ----------
         2          1         75
         2          2         55
         2          3          7
         2          4          7
         4          1         75
         4          2         55
         4          3          7
         4          4          7
         2          5        1009 rows selected.

Answer 26

忘了贴测试用sql代码了，不好意思drop table c;
drop table sc;
create table C(C# int, CN varchar(10));
insert into C values(1, '厚黑学') ;
insert into C values(2, '查询基础'); 
insert into C values(3, '能说') ;
insert into C values(4, '会道') ;
create table SC(S# int, C# int, G int);
insert into SC values(1 ,1 ,1 );
insert into SC values(1 ,3 ,6 );
insert into SC values(2 ,1 ,75) ;
insert into SC values(2 ,2 ,55) ;
insert into SC values(2 ,3 ,7 );
insert into SC values(2 ,4 ,7 );
insert into SC values(3 ,3 ,77) ;
insert into SC values(4 ,1 ,75) ;
insert into SC values(4 ,2 ,55);
insert into SC values(4 ,3 ,7 );
insert into SC values(4 ,4 ,7 );
insert into SC values(6 ,6 ,6 );
insert into SC values(7 ,4 ,7 );
select * from sc a
where not exists (select 1 from c where not exists (select 1 from sc b where b.s#=a.s# and b.c#=c.c#));
insert into sc values(2,5,100);
select * from sc a
where not exists (select 1 from c where not exists (select 1 from sc b where b.s#=a.s# and b.c#=c.c#));drop table c;
drop table sc;
create table C(C# int, CN varchar(10));
insert into C values(1, '厚黑学') ;
insert into C values(2, '查询基础'); 
insert into C values(3, '能说') ;
insert into C values(4, '会道') ;
create table SC(S# int, C# int, G int);
insert into SC values(1 ,1 ,1 );
insert into SC values(1 ,3 ,6 );
insert into SC values(2 ,1 ,75) ;
insert into SC values(2 ,2 ,55) ;
insert into SC values(2 ,3 ,7 );
insert into SC values(2 ,4 ,7 );
insert into SC values(3 ,3 ,77) ;
insert into SC values(4 ,1 ,75) ;
insert into SC values(4 ,2 ,55);
insert into SC values(4 ,3 ,7 );
insert into SC values(4 ,4 ,7 );
insert into SC values(6 ,6 ,6 );
insert into SC values(7 ,4 ,7 );
select * from sc a
where not exists (select 1 from c where not exists (select 1 from sc b where b.s#=a.s# and b.c#=c.c#));
insert into sc values(2,5,100);
select * from sc a
where not exists (select 1 from c where not exists (select 1 from sc b where b.s#=a.s# and b.c#=c.c#));

Answer 27

如果只要显示c表中的有的，可以加一个条件
测试如下：
 
create table C(C# int, CN varchar(10)); 
insert into C values(1, '厚黑学') ; 
insert into C values(2, '查询基础');  
insert into C values(3, '能说') ; 
insert into C values(4, '会道') ; 
create table SC(S# int, C# int, G int); 
insert into SC values(1 ,1 ,1 ); 
insert into SC values(1 ,3 ,6 ); 
insert into SC values(2 ,1 ,75) ; 
insert into SC values(2 ,2 ,55) ; 
insert into SC values(2 ,3 ,7 ); 
insert into SC values(2 ,4 ,7 ); 
insert into SC values(3 ,3 ,77) ; 
insert into SC values(4 ,1 ,75) ; 
insert into SC values(4 ,2 ,55); 
insert into SC values(4 ,3 ,7 ); 
insert into SC values(4 ,4 ,7 ); 
insert into SC values(6 ,6 ,6 ); 
insert into SC values(7 ,4 ,7 ); 
select * from sc a 
where not exists (select 1 from c where not exists (select 1 from sc b where b.s#=a.s# and b.c#=c.c#)); 
insert into sc values(2,5,100); 
select * from sc a 
where not exists (select 1 from c where not exists (select 1 from sc b where b.s#=a.s# and b.c#=c.c# )) and exists(select 1 from c where a.c#=c.c#); 
drop table c; 
drop table sc;Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.        S#         C#          G
---------- ---------- ----------
         2          1         75
         2          2         55
         2          3          7
         2          4          7
         4          1         75
         4          2         55
         4          3          7
         4          4          78 rows selected.
1 row created.        S#         C#          G
---------- ---------- ----------
         4          1         75
         2          1         75
         4          2         55
         2          2         55
         4          3          7
         2          3          7
         4          4          7
         2          4          78 rows selected.
Table dropped.
Table dropped.