22,207
社区成员
发帖
与我相关
我的任务
分享
select * from b b1 where not exists
(select 1 as aflag from a left join (select * from b where b.divcode=b1.divcode) b2 on a.fid=b2.fid
where isnull(b2.aflag ,0) = 0 )
现有两张表
create table C(C# int,CN varchar)
create table SC(S# int,C# int,G int)
表中数据如下
C表
C# CN
1 厚黑学
2 查询基础
3 能说
4 会道
http://topic.csdn.net/u/20080313/21/acb7f550-8ce7-4352-96dc-4715a0e43287.html?398509657
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
-----------------------------------------------
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)
/*
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 行)
*/
--------------------------------------------------------------
--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
create table a(fid varchar(10), fname varchar(10))
insert into a values('001', '公式A')
insert into a values('002', '公式B')
create table b(divcode varchar(10),fname varchar(10),fid varchar(10), aflag int)
insert into b values('0001' , '单位A' , '001' , 1)
insert into b values('0001' , '单位A' , '002' , 1)
insert into b values('0002' , '单位B' , '001' , 1)
insert into b values('0002' , '单位B' , '002' , 0)
insert into b values('0003' , '单位C' , '002' , 1)
go
select fname from b where aflag = 1 group by fname having count(1) = (select count(1) from a)
drop table a , b
/*
fname
----------
单位A
(所影响的行数为 1 行)
*/
最好给出完整的表结构,测试数据,计算方法和正确结果.