• 主页
• 基础类
• 应用实例
• 新技术前沿

zoujinhe 2003-12-27 12:58:49

a是主表，b和c都是子表
a b c (表名)
---------------------------------
SN SN Detail SN Detail （字段名)
------------------------------------------
1 1 fdf 2 gfgdg
2 1 342 3 dfsdfs
3 2 ewrew 3 fdsfsdf

SN count(b) count(c)
--------------------
1 2 0
2 0 1
3 0 2

Select a.sn,count(b.sn), count(c.sn)
from a left join b on a.sn=b.sn
left join c on a.sn=c.sn
group by a.sn

...全文
25 点赞 收藏 21

21 条回复

zoujinhe 2003-12-30
up

netcoder 2003-12-29

zoujinhe 2003-12-29

SN count(b) count(c)
--------------------
1 2 0
2 1 1
3 0 2

Jianli2004 2003-12-27
Select a.sn,count(b.sn), count(c.sn)
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

zoujinhe 2003-12-27
select SN,
(select count(*) from b where SN = a.SN),
(select count(*) from c where SN = a.SN)
from a

Jianli2004 2003-12-27
select SN,
(select count(*) from b where SN = a.SN),
(select count(*) from c where SN = a.SN)
from a

xiaocuo_zrf 2003-12-27

cgsun 2003-12-27
Pardox,是啥

zoujinhe 2003-12-27

{zjcxc(邹建) 哥哥好厉害
=====================

zoujinhe 2003-12-27

select SN,
(select count(1) from b where SN = a.SN),
(select count(1) from c where SN = a.SN)
from a

realgz 2003-12-27

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

erigido 2003-12-27

xiaocuo_zrf 2003-12-27

gmlxf 2003-12-27
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

-- 卡看可以用吗？我也不知道。sql是可以。

dlpseeyou 2003-12-27
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

zoujinhe 2003-12-27

shuiniu 2003-12-27

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 行）
*/

realgz 2003-12-27

gmlxf 2003-12-27
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

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区