分类统计 谢谢
有两张表
有数据表cag
columns name
1 a
2 b
3 c
4 d
5 e
6 f
. .
. .
. .
数据表 bb
columns title
1 dd
1 ss
1 qq
2 mm
2 pp
. .
. .
. .
根据两张表 我要得到这么一个结果
name count
a 3
b 2
c 0 (或不显示)
d 0
. .
. .
. .
columns 共有70多个
[解决办法]
--创建测试环境
declare @cag table(
[columns] int,
[name] varchar(10)
)
declare @bb table(
[columns] int,
title varchar(10)
)
insert into @cag
select 1, 'a ' union all
select 2, 'b ' union all
select 3, 'c ' union all
select 4, 'd ' union all
select 5, 'e ' union all
select 6, 'f '
insert into @bb
select 1, 'dd ' union all
select 1, 'ss ' union all
select 1, 'qq ' union all
select 2, 'mm ' union all
select 2, 'pp '
--查询
select [name],sum(case when title is null then 0 else 1 end) [count]
from @cag c left join @bb b on c.columns=b.columns
group by [name]
