• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

简单的count

sunhood 2008-02-20 06:08:06
有3个表都有编号列,统计每个表中同一编号的记录数。
大致如下:


表1 表2 表3
code code code
------- ----------- -----------
ABCD ABCD
ABCD ABCD
ABCD
ABCD


希望得到如下结果

code 表1(此处用a1,a2标识也可) 表2 表3
------- ----------- ----------- -------
ABCD 4 2 0
...全文
57 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
cxmcxm 2008-02-20
改改9楼的
select 
code,
[表1]=sum(case when TName='表1' then 1 else 0 end),
[表2]=sum(case when TName='表2' then 1 else 0 end),
[表3]=sum(case when TName='表3' then 1 else 0 end)

from
( select code ,'表1' as TName from 表1
union
select code ,'表2'from 表2
union
select code ,'表3'from 表3 )T
group by code
回复
-狙击手- 2008-02-20
elect 
code,
[表1]=sum(case when TName='表1' then 1 else 0 end),
[表2]=sum(case when TName='表2' then 1 else 0 end),
[表3]=sum(case when TName='表3' then 1 else 0 end)

from
( select code ,'表1' as TName from 表1
union
select code ,'表2'from 表2
union
select code ,'表3'from 表3 )T
group by code
回复
cxmcxm 2008-02-20
select code=case when a.code is not null then a.code else 
case when b.code is not null then b.code else c.code end end,
a1=isnull(a.a1,0),a2=isnull(b.a2,0),a3=isnull(c.a3,0)

from
(select code,count(*) a1 from 表1 group by code) a full join
(select code,count(*) a2 from 表2 group by code) b on a.code=b.code full join
(select code,count(*) a3 from 表3 group by code) c on a.code=c.code and b.code=c.code
回复
中国风 2008-02-20

select
code,
[表1]=sum(case when TName='表1' then 1 else 0 end),
[表2]=sum(case when TName='表2' then 1 else 0 end),
[表3]=sum(case when TName='表3' then 1 else 0 end)

from
( select code ,'表1' as TName from 表1
union
select code ,'表2'from 表2
union
select code ,'表3'from 表3 )T
where
code='abcd'
回复
ruihuahan 2008-02-20
select ....
from (select code, count(*) from table1) t1
inner join (select code, count(*) from table2) t2
on t1.code=t2.code
inner join (select code, count(*) from table3) t3
on t2.code=t3.code
回复
sunhood 2008-02-20
有没有更简便的写法??
回复
liangCK 2008-02-20
就3楼的去写吧.
回复
sunhood 2008-02-20
用left outer join或union 我可以写,但是尽量不想用left outer 或者union,该怎么写?
回复
sunhood 2008-02-20
code列中会有很多编码比如:ABCD, AAAA,BBBB.......该怎么count?
回复
liangCK 2008-02-20
select code
,表1=(select count(code) from 表1 where code=a.code)
,表2=(select count(code) from 表2 where code=a.code)
,表3=(select count(code) from 表3 where code=a.code)
from
(
select code from 表1
union
select code from 表2
union
select code from 表3
) a
回复
wzy_love_sly 2008-02-20
select code='abcd',
a1=(select count(1) from 表1 where code='abcd'),
a2=(select count(1) from 表2 where code='abcd'),
a3=(select count(1) from 表3 where code='abcd')
回复
wzjpsq 2008-02-20
left join
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-02-20 06:08
社区公告
暂无公告