select SN,
[count(b)] = (select count(1) from b where SN = a.SN),
[count(c)] = (select count(1) from c where SN = a.SN)
from a
[count(b)] = (select count(1) from b where SN = a.SN),
[count(c)] = (select count(1) from c where SN = a.SN)
from a
declare @tablea table(sn int)
insert @tablea values(1)
insert @tablea values(2)
insert @tablea values(3)declare @tableb table(sn int ,detail char(5))
insert @tableb values(1,'fdf')
insert @tableb values(1,'342')
insert @tableb values(2,'ewrew')declare @tablec table(sn int ,detail char(5))
insert @tablec values(2,'dfdf')
insert @tablec values(3,'dfd')
insert @tablec values(3,'fdds')
select a.sn,
(select count(*) from @tableb where a.sn = sn) [count(b)],
(select count(*) from @tablec where a.sn = sn) [count(c)]
from @tablea a/*
sn count(b) count(c)
----------- ----------- -----------
1 2 0
2 1 1
3 0 2(所影响的行数为 3 行)
*/
from a
[count(b)] = (select count(1) from b where SN = a.SN),
[count(c)] = (select count(1) from c where SN = a.SN)
from a-- 卡看可以用吗?我也不知道。sql是可以。
Select a.sn,(count(*) -sum(isnull(b.sn,1))) sn_b,(count(*) -sum(isnull(c.sn,1))) sn_c
from a left join b on a.sn=b.sn
left join c on a.sn=c.sn
group by a.sn
(select count(1) from b where SN = a.SN),
(select count(1) from c where SN = a.SN)
from a到是可以通过,但统计出来全是0。另外: pradox 是不支持isnull的.
=====================
学习~~}呵呵,我也姓邹。
(select count(*) from b where SN = a.SN),
(select count(*) from c where SN = a.SN)
from aparadox不支持count(1)
(select count(*) from b where SN = a.SN),
(select count(*) from c where SN = a.SN)
from a是可以通过,但统计出来全是0。
from a left join b on a.sn=b.sn
left join c on a.sn=c.sn
group by a.sn 没有问题根据你的数据结果应该为sn
----------- ----------- -----------
1 2 0
2 1 1
3 0 2
怎么得出
SN count(b) count(c)
--------------------
1 2 0
2 1 1
3 0 2