Select
行业ID=A.行业ID,
行业名称=A.行业名称,
商机数目=count(B.行业ID)
from 表a A left join 表b B
on A.行业ID=B.行业ID
Group by A.行业ID,A.行业名称
行业ID=A.行业ID,
行业名称=A.行业名称,
商机数目=count(B.行业ID)
from 表a A left join 表b B
on A.行业ID=B.行业ID
Group by A.行业ID,A.行业名称
select a.行业ID,
a.行业名称,
商机数目=(select count(1) from 表b where 行业ID=a.行业ID group by 行业ID)
from 表a
union
select a.行业ID,
a.行业名称,
商机数目=0
from 表a
where 表a.行业ID not in(select distinct 行业ID from 表b )
declare @表a table(行业ID int ,行业名称 varchar(20))
insert into @表a select 11,'农业'
union all select 12,'工业'
union all select 13,'服务业'
union all select 14,'林业'declare @表b table(商机ID int ,行业ID int)
insert into @表b select 1,11
union all select 2,11
union all select 3,12
union all select 4,13
union all select 5,12
union all select 6,11--查询
Select
行业ID=A.行业ID,
行业名称=A.行业名称,
商机数目=count(B.行业ID)
from @表a A left join @表b B
on A.行业ID=B.行业ID
Group by A.行业ID,A.行业名称
Order by count(B.行业ID) desc
--结果
/*
行业ID 行业名称 商机数目
----------- -------------------- -----------
11 农业 3
12 工业 2
13 服务业 1
14 林业 0(所影响的行数为 4 行)
*/
select a.行业ID ,a.行业名称,(select count(*) from b where 行业ID =a.行业ID ) as 商机数目 from a
insert into @表a select 11,'农业'
union all select 12,'工业'
union all select 13,'服务业'
union all select 14,'林业'declare @表b table(商机ID int ,行业ID int)
insert into @表b select 1,11
union all select 2,11
union all select 3,12
union all select 4,13
union all select 5,12
union all select 6,11
select A.行业ID,A.行业名称,(select count(1) from @表b where 行业ID=A.行业ID) as 商机数目
from @表a A
select a.行业ID ,a.行业名称,(select count(*) from b where 行业ID =a.行业ID ) as 商机数目 from a