查询条件:用户类型,用户组,统计出有多少个用户.所有类型都必须显示数据,就是说在该类型下没有用户,那么它应该显示统计值0--类型表
create table c
(
[id] int primary key identity,
[Name] varchar(20)
)insert into c values('aaa')
insert into c values('bbb')
insert into c values('ccc')--用户表
drop table d
create table d
(
[id] int primary key identity,
[type] int,
[Name] varchar(20),
[tag] int,
c_id int
)
insert into d values(1,'asdsecxss',1,1)
insert into d values(1,'sfdsf',1,1)
insert into d values(3,'fgfghg',2,2)
insert into d values(3,'zxczxcxzczx',3,2)
insert into d values(3,'wewer',3,2)
insert into d values(3,'gdfgtsssd',4,3)--用户组表
create table e
(
[id] int primary key identity,
[group_id] int
)insert into e values(1)
insert into e values(1)
insert into e values(2)
insert into e values(3)--以下是查询select sum(case when dd.id is null then 0 else 1 end)as count,
cc.id as type from d dd
right join c cc on cc.id=dd.type and dd.tag=2
left join e on dd.c_id = e.id and e.group_id=3 --这句话不起作用,不管换1还是2、3都没作用
group by cc.id查询结果为
--------------
counts type
0 1
0 2
1 3正确的结果应该为--------------
counts type
0 1
0 2
0 3
create table c
(
[id] int primary key identity,
[Name] varchar(20)
)insert into c values('aaa')
insert into c values('bbb')
insert into c values('ccc')--用户表
drop table d
create table d
(
[id] int primary key identity,
[type] int,
[Name] varchar(20),
[tag] int,
c_id int
)
insert into d values(1,'asdsecxss',1,1)
insert into d values(1,'sfdsf',1,1)
insert into d values(3,'fgfghg',2,2)
insert into d values(3,'zxczxcxzczx',3,2)
insert into d values(3,'wewer',3,2)
insert into d values(3,'gdfgtsssd',4,3)--用户组表
create table e
(
[id] int primary key identity,
[group_id] int
)insert into e values(1)
insert into e values(1)
insert into e values(2)
insert into e values(3)--以下是查询select sum(case when dd.id is null then 0 else 1 end)as count,
cc.id as type from d dd
right join c cc on cc.id=dd.type and dd.tag=2
left join e on dd.c_id = e.id and e.group_id=3 --这句话不起作用,不管换1还是2、3都没作用
group by cc.id查询结果为
--------------
counts type
0 1
0 2
1 3正确的结果应该为--------------
counts type
0 1
0 2
0 3
有人看出来了吗
应该先是d和e连接,再和c外连接。
select c.name,count(d.id) as count
from d join e on d.dd.c_id = e.id and e.group_id=3
right join c on d.type=c.id and d.tag=2
from c cc left join d dd on cc.id=dd.type and dd.tag=2
left join e ee on dd.c_id=ee.id
group by cc.id
/*
type count
1 0
2 0
3 1
*/是有一条记录
select sum(case when dd.id is null then 0 else 1 end)as count,cc.id as type
from d dd
left join c cc on cc.id=dd.type
left join e on dd.c_id = e.id
group by cc.id
--类型表
create table c
(
[id] int primary key identity,
[Name] varchar(20)
)insert into c values('aaa')
insert into c values('bbb')
insert into c values('ccc')--用户表
create table d
(
[id] int primary key identity,
[type] int,
[Name] varchar(20),
[tag] int,
c_id int
)
insert into d values(1,'asdsecxss',1,1)
insert into d values(1,'sfdsf',1,1)
insert into d values(3,'fgfghg',2,2)
insert into d values(3,'zxczxcxzczx',3,2)
insert into d values(3,'wewer',3,2)
insert into d values(3,'gdfgtsssd',4,3)--用户组表
create table e
(
[id] int primary key identity,
[group_id] int
)insert into e values(1)
insert into e values(1)
insert into e values(2)
insert into e values(3)select * from c
select * from d
select * from eselect
count(d.id) as count,c.id as type
from
d
join e on
d.c_id = e.id
and
e.group_id=3
right join c on
d.type=c.id and d.tag=2
group by
c.iddrop table c
drop table d
drop table e/*count type
----------- -----------
0 1
0 2
0 3
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
*/
你的统计没用到e表,所以它对整个统计结果没影响tryselect
sum(case when dd.id is null or e.id is null then 0 else 1 end)as count,
cc.id as type
from d dd
right join c cc on cc.id=dd.type and dd.tag=2
left join e on dd.c_id = e.id and e.group_id=3 --这里用1和2、3是不同的
group by cc.id
select c.id,count(d.id) as count
from d join e on d.c_id = e.id and e.group_id=1
right join c on d.type=c.id and d.tag=2
group by c.id
id count
1 0
2 0
3 1
group_id取其它值时,均为0,应该满足你的要求吧。
(
[id] int primary key identity,
[Name] varchar(20)
)insert into c values('aaa')
insert into c values('bbb')
insert into c values('ccc')--用户表
drop table d
create table d
(
[id] int primary key identity,
[type] int,
[Name] varchar(20),
[tag] int,
c_id int
)
insert into d values(1,'asdsecxss',1,1)
insert into d values(1,'sfdsf',1,1)
insert into d values(3,'fgfghg',2,2)
insert into d values(3,'zxczxcxzczx',3,2)
insert into d values(3,'wewer',3,2)
insert into d values(3,'gdfgtsssd',4,3)--用户组表
create table e
(
[id] int primary key identity,
[group_id] int
)insert into e values(1)
insert into e values(1)
insert into e values(2)
insert into e values(3)
---条件类型和组,结果统计类型下面的用户人数
--测试条件1
declare @tag int
set @tag=2declare @group_id int
set @group_id=3select counts=count(m.id),type=c.id from c left join
(
select d.*,group_id from d join e on d.c_id=e.id where d.tag=@tag and e.group_id=@group_id
) m
on c.id=m.type group by c.id/*
counts type
----------- -----------
0 1
0 2
0 3
*/
---测试条件2
declare @tag int
set @tag=1declare @group_id int
set @group_id=1select counts=count(m.id),type=c.id from c left join
(
select d.*,group_id from d join e on d.c_id=e.id where d.tag=@tag and e.group_id=@group_id
) m
on c.id=m.type group by c.id/*
counts type
----------- -----------
2 1
0 2
0 3
*/