请教大家两个表A, BA: B:
a, b a, b
-------- ------
AA, 11 AA, 22
AA, 22 BB, 33
BB, 33 BB, 44
BB, 44 DD. 66
CC, 55
DD, 66
EE, 77
--------------------------------请问用怎样的SQL语句才能选出如下的结果呢?
(要求表A的column a的所有行都在表B中)
a b
-------
BB, 33
BB, 44
DD, 66非常感谢!
a, b a, b
-------- ------
AA, 11 AA, 22
AA, 22 BB, 33
BB, 33 BB, 44
BB, 44 DD. 66
CC, 55
DD, 66
EE, 77
--------------------------------请问用怎样的SQL语句才能选出如下的结果呢?
(要求表A的column a的所有行都在表B中)
a b
-------
BB, 33
BB, 44
DD, 66非常感谢!
create table B(a varchar(10),b int)insert A select 'AA',11
union all select 'AA',22
union all select 'BB',33
union all select 'BB',44
union all select 'CC',55
union all select 'DD',66
union all select 'EE',77
insert B select 'AA',22
union all select 'BB',33
union all select 'BB',44
union all select 'DD',66select * from B where a in
(
select distinct n.a from
(
select a1.a,cnt=sum(a1.b) from A a1 group by a1.a
)m
inner join
(
select b1.a,cnt=sum(b1.b) from B b1 group by b1.a
)n
on m.cnt=n.cnt
)drop table A,B/*
a b
---------- -----------
BB 33
BB 44
DD 66
*/
declare @tb table(a varchar(10),b int)
insert @ta
select 'AA', 11 union all
select 'AA', 22 union all
select 'BB', 33 union all
select 'BB', 44 union all
select 'CC', 55 union all
select 'DD', 66 union all
select 'EE', 77
insert @tb
select 'AA', 22 union all
select 'BB', 33 union all
select 'BB', 44 union all
select 'DD', 66SELECT * FROM @ta AS t WHERE not exists(select 1 from
(select x.a,y.b from @ta as x left join @tb as y on x.a = y.a and x.b = y.b) as a where a = t.a and b is null)/*结果
a b
-------
BB, 33
BB, 44
DD, 66
*/
create table B(a varchar(10),b int)insert A select 'AA',11
union all select 'AA',22
union all select 'BB',33
union all select 'BB',44
union all select 'CC',55
union all select 'DD',66
union all select 'EE',77
insert B select 'AA',22
union all select 'BB',33
union all select 'BB',44
union all select 'DD',66select * from A where exists ( select * from B where A.a =B.a and A.b=B.b) and A.a not in (
select A.a from A where not exists ( select * from B where A.a =B.a and A.b=B.b))
where exists (
select 1 from a
where a=b.a
and b=b.b
)
and not exists (
select 1 from a
where a=b.a
and b<>b.b
)
where exists (
select 1 from a
where a=b.a
and b=b.b
)
and not exists (
select 1 from a a1
where a1.a=b.a
and a1.b<>b.b
and not exists (
select 1 from b
where a=a1.a
and b=a1.b
)
)
declare @tb table(a varchar(10),b int)
insert @ta
select 'AA', 11 union all
select 'AA', 22 union all
select 'BB', 33 union all
select 'BB', 44 union all
select 'CC', 55 union all
select 'DD', 66 union all
select 'EE', 77
insert @tb
select 'AA', 22 union all
select 'BB', 33 union all
select 'BB', 44 union all
select 'DD', 66select * from @tb b
where exists (
select 1 from @ta
where a=b.a
and b=b.b
)
and not exists (
select 1 from @ta a1
where a1.a=b.a
and a1.b<>b.b
and not exists (
select 1 from @tb
where a=a1.a
and b=a1.b
)
)--结果
a b
---------- -----------
BB 33
BB 44
DD 66(所影响的行数为 3 行)
where a not in
(select a.a from a
left join b
on b.a=a.a and b.b=a.b
where b.a is null)
create table b(a varchar(10),b int)
go
insert a select 'aa',11
union all select 'aa',22
union all select 'bb',33
union all select 'bb',44
union all select 'cc',55
union all select 'dd',66
union all select 'ee',77
insert b select 'aa',22
union all select 'bb',33
union all select 'bb',44
union all select 'dd',66
go
select * from a a1
where not exists(select 1 from b inner join
a a2 on b.a=a2.a and b.b = a2.b right join
a a3 on a3.a = a2.a and a3.b=a2.b
where a2.a is null
and a3.a = a1.a)a b
- -----------
bb 33
bb 44
dd 66
create table B(a varchar(10),b int)insert A select 'AA',11
union all select 'AA',22
union all select 'BB',33
union all select 'BB',44
union all select 'CC',55
union all select 'DD',66
union all select 'EE',77
insert B select 'AA',22
union all select 'BB',33
union all select 'BB',44
union all select 'DD',66select a.* from a
left join
(select a.a from a
left join b
on b.a=a.a and b.b=a.b
where b.a is null)b
on b.a=a.a
where b.a is nullselect dd.* from a aa
full join b bb
on bb.a=aa.a and bb.b=aa.b
left join a cc
on cc.a=aa.a and bb.a is null
right join a dd
on dd.a=cc.a and dd.b=cc.b
where cc.a is null