求救,请各位帮忙
有两张表a,b 其中表a是表b的子集,内容如下:
表a
id xb mm
1 11 111
2 22 222
3 33 333
4 44 444
.....
表b
id xb mm
1 11 111
2 11 112
3 22 222
4 22 221
5 33 333
6 44 444
7 55 555
.....
现要把表a中xb字段和表b中xb字段相同且总数一样的记录找出来,请问sql语句应该怎么写,谢谢了!!!!
也就是说要把
xb为33和44的记录找出来
------解决方案------------------------创建测试数据
declare @ta table(id int,xb int,mm int)
insert @ta
select 1, 11, 111 union all
select 2, 22, 222 union all
select 3, 33, 333 union all
select 4, 44, 444
declare @tb table(id int,xb int,mm int)
insert @tb
select 1, 11, 111 union all
select 2, 11, 112 union all
select 3, 22, 222 union all
select 4, 22, 221 union all
select 5, 33, 333 union all
select 6, 44, 444 union all
select 7, 55, 555
----查询
select xb from @ta as a group by xb having count(*) = (select count(*) from @tb where xb = a.xb)
/*结果
xb
-----------
33
44
*/
------解决方案--------------------再改下
select t1.* from t1
join
(
select xb,count(*)as i
from t2
group by xb
having count(*)=(select count(*) from t1 where t1.xb=t2.xb group by xb)
)a on t1.xb=a.xb
id xb mm
----------- ----------- -----------
3 33 333
4 44 444
5 66 1589
6 66 1478
(4 row(s) affected)