table1(ID,BH,ZD)ID BH ZD
------------------
1 X zd1
2 X zd2
3 Y
4 Z zd4table2(key1,FID) 其中FID关联到table1.IDkey1 FID
-------------
k1 1
k2 1
k3 2
k4 3
k5 4
问题:输出:
不重复的table1.BH的总数,要求table1.ZD不为空,且table1.BH对应的ID在table2中有table2.FID如上表中的数据,则输出为2 (X,Z)select count(distinct bh)
from table1
where (table1.ZD is not null)
and exists (select * from table2 where table2.FID (???这里怎么写?))我觉得 select * from table2 where table2.FID in select ID from table1
如果这样写,肯定是不对的。应该还要判断是否为BH对应的ID,这样我就乱了...谢谢
select count(distinct a.bh)
from (
select *
from table1
where (table1.ZD is not null)
) a
inner join
table2 b
on a.id=b.fid
insert @t1
select 1,'X','zd1' union all
select 2,'X','zd2' union all
select 3,'Y',NULL union all
select 4,'Z','zd4'
declare @t2 table(key1 varchar(10),fid int)
insert @t2
select 'k1',1 union all
select 'k2',1 union all
select 'k3',2 union all
select 'k4',3 union all
select 'k5',4select count(distinct BH) from @t1 where zd is not null and id in (select fid from @t2)
或
select count(distinct BH) from @t1 a inner join @t2 b on a.id = b.fid where a.zd is not null
from table1
where (table1.ZD is not null)
and table1.id in(select distinct table2.fid from table2 )
select count(a.BH) from table1 a
where a.zd is not null
and not exists (select 1 from table2 where a.id = b.id)
group by a.bh
where a.zd is not null
and exists (select 1 from table2 where a.id = b.fid)
group by a.bh