求救,请各位帮忙

[Daem0n]

有两张表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的记录找出来

[Daem0n]

楼上的不对呀

[Daem0n]

hellowork(一两清风)

为什么会用到mm字段来判断,能讲一下吗,谢谢

[fa_ge]

create table t1
(id int, xb int, mm int)
insert into t1
select 1, 11, 111 union all
select 2, 22, 222 union all
select 3, 33, 333 union all
select 4, 44, 444


create table t2

(id int, xb int , mm int )
insert into t2
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 t1.* from t1
join
(
select xb,count(*)as i from t2 group by xb having count(*)=1
)a on t1.xb=a.xb

id xb mm
----------- ----------- -----------
3 33 333
4 44 444

(2 row(s) affected)

[hellowork]

之所以用mm判断是因为楼主并没有说明"总数"指的是什么,所以就假设在B表中必须是xb相等并且mm也相等.
请楼主说明"总数"的含义.

[Daem0n]

可能是我没说清楚

表b包含表a的所有数据,这里表a中xb为33和44的只有1条记录,表b中xb为33和44的也只有1条记录,所以就要把这样的33和44找出来,如果表a中xb为66的记录有2条,表b中xb为66的记录也有2条,就也要找出来,谢谢各位!!

[hellowork]

----创建测试数据
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
*/

[fa_ge]

再改下

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)

[fa_ge]

sf,吃完再來看

[hellowork]

----创建测试数据
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 * from @ta as a where not exists(select 1 from @tb where xb = a.xb and mm <> a.mm)

/*结果
id xb mm
----------- ----------- -----------
3 33 333
4 44 444
*/

[tom_cat007]

--总数指记录数还是mm字段？ 如果是记录数可用下面的sql
select * from a where where xb in (
select a.xb from
(select xb,count(*) f_cnt from a group by xb ) a1 ,
(select xb,count(*) f_cnt from b group by xb ) b1
where a1.xh = b1.xb and a1.f_cnt = b1.f_cnt )