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

关于COUNT的简单问题(是Pradox的)

zoujinhe 2003-12-27 12:58:49
三个表 a,b,c
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

是可以通过,但统计出来全是0。
回复
Jianli2004 2003-12-27
select SN,
(select count(*) from b where SN = a.SN),
(select count(*) from c where SN = a.SN)
from a

paradox不支持count(1)
回复
xiaocuo_zrf 2003-12-27
好像不行,只能用Union
回复
cgsun 2003-12-27
Pardox,是啥
回复
zoujinhe 2003-12-27
不是吧,都有人说:

{zjcxc(邹建) 哥哥好厉害
=====================
学习~~}

呵呵,我也姓邹。

回复
老实一点

我没用过pradox

回复
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

到是可以通过,但统计出来全是0。

另外: pradox 是不支持isnull的.
回复
realgz 2003-12-27
原来如此 paradox 支不支持 isnull?
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
各位大佬都比较忙,看看标题头好吗?

是Pradox,不支持CASE的。
回复
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
对 或者用 sum(case when b.sn is null then 0 else 1 end) as [count(b)]
回复
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
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告