表a
id
100
101
102
103
104
105
表b
id flag
100 1
100 2
100 2
100 3
101 2
102 1
102 3
104 2要得到的结果
100 2
101 1
102 0
103 0
104 1
105 0
就是说查出b表中flag等于2的记录数
小弟想出的办法是select时sum(case when b.flag = 2 then 1 else 0 end)
但是感觉似乎不太好...请教下高级写法....
id
100
101
102
103
104
105
表b
id flag
100 1
100 2
100 2
100 3
101 2
102 1
102 3
104 2要得到的结果
100 2
101 1
102 0
103 0
104 1
105 0
就是说查出b表中flag等于2的记录数
小弟想出的办法是select时sum(case when b.flag = 2 then 1 else 0 end)
但是感觉似乎不太好...请教下高级写法....
from tba a
left join tbb b
on a.id=b.id and b.flag=2
group by a.id
count(b.id)
from a left join b
on a.id=b.id
group by a.id
declare @表a table (id int)
insert into @表a
select 100 union all
select 101 union all
select 102 union all
select 103 union all
select 104 union all
select 105
--> 测试数据: @表b
declare @表b table (id int,flag int)
insert into @表b
select 100,1 union all
select 100,2 union all
select 100,2 union all
select 100,3 union all
select 101,2 union all
select 102,1 union all
select 102,3 union all
select 104,2select a.id,count(b.id) cnt
from @表a a
left join @表b b
on a.id=b.id and b.flag=2
group by a.id/*
id cnt
----------- -----------
100 2
101 1
102 0
103 0
104 1
105 0(所影响的行数为 6 行)
*/
count(b.id)
from a left join b
on a.id=b.id and b.flag=2
group by a.id
select t1.id,count(t2.id) from #tb1 t1 left join #tb2 t2
on t1.id = t2.id and t2.flag = 2
group by t1.id