表 a
EstateID int
CountyID int
表b
CountyID int表b数据:
1
2
3
4
表a数据:
1 1
2 1
3 1
4 2
5 3
现在统计每个CountyID对应多少EstateID,现在因为表b的数据4在表a中没有数据,怎么在统计时,显示为0呢
select count(EstateID) as County_Count , CountyID
from a
group by CountyID
order by CountyID
EstateID int
CountyID int
表b
CountyID int表b数据:
1
2
3
4
表a数据:
1 1
2 1
3 1
4 2
5 3
现在统计每个CountyID对应多少EstateID,现在因为表b的数据4在表a中没有数据,怎么在统计时,显示为0呢
select count(EstateID) as County_Count , CountyID
from a
group by CountyID
order by CountyID
CountyID Count
1 3
2 1
3 1
4 0
b left join a on b.CountyID=a.CountyID
group by a.CountyID
order by a.CountyID
declare @A table([aid] int,[bid] int)
Insert @A
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,2 union all
select 5,3declare @B table([bid] int)
Insert @B
select 1 union all
select 2 union all
select 3 union all
select 4
Select b.bid, isnull(a.cnt,0) as c from @B b
left join (select bid,count(1) as cnt from @A group by bid ) a on a.bid = b.bid
/*
bid c
----------- -----------
1 3
2 1
3 1
4 0*/
insert @a select
1, 1 union select
2, 1 union select
3, 1 union select
4, 2 union select
5, 3
declare @b table(CountyID int)
insert @b select
1 union all select
2 union all select
3 union all select
4
select b.countyid,isnull(a.cnt,0) c from @b b
left join (select countyid,count(1)as cnt from @a group by countyid)a on a.countyid=b.countyid