27,579
社区成员
发帖
与我相关
我的任务
分享
--2
select b.bname,count(*) cnt
from table1 a join table2 b on a.code = b.code
left join table3 c on b.bname = c.bname
where a.name = '美食频道'
group by b.bname
--1
select a.name,count(*) cnt
from table1 a left join table2 b on a.code = b.code
group by a.name
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([id] int,[code] varchar(3),[name] varchar(8))
insert [table1]
select 1,'001','美食频道' union all
select 2,'002','音乐频道' union all
select 3,'003','购物频道'
go
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([bid] int,[code] varchar(3),[bname] varchar(12))
insert [table2]
select 1,'001','西餐' union all
select 2,'001','中餐' union all
select 3,'002','动感地带音乐'
go
if object_id('[table3]') is not null drop table [table3]
go
create table [table3]([did] int,[bname] varchar(12),[loginname] varchar(2))
insert [table3]
select 1,'西餐','xy' union all
select 2,'动感地带音乐','zw'
go
select a.name as 频道名,isnull(t.cnt,0) as 访问次数
from table1 a
left join
(select b.code,count(1) as cnt from table2 b
join table3 c on b.bname=c.bname
group by b.code) t
on a.code=t.code
/**
频道名 访问次数
-------- -----------
美食频道 1
音乐频道 1
购物频道 0
(3 行受影响)
**/
select b.bname as 业务名,isnull(t.cnt,0) as 访问次数
from table2 b
join table1 a on a.code=b.code and a.name='美食频道'
left join
(select bname,count(1) as cnt from table3
group by bname) t
on b.bname=t.bname
/**
业务名 访问次数
------------ -----------
西餐 1
中餐 0
(2 行受影响)
**/