27,579
社区成员
发帖
与我相关
我的任务
分享
with tb1(id,userid,content) as
(
select 1,2,333 union all
select 2,3,444 union all
select 3,4,555 union all
select 4,5,555
),
tb2(userid,groupid) as--分组与userid对照表
(
select 2,1 union all
select 3,1 union all
select 4,2 union all
select 5,6
),
tb3(groupid) as--分组表
(
select 1 union all
select 2 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
)
select tb3.groupid 分组,count(tb1.id) 数量
from tb3
left join tb2 on tb3.groupid = tb2.groupid
left join tb1 on tb2.userid = tb1.userid
group by tb3.groupid
/*
分组 数量
----------- -----------
1 2
2 1
4 0
5 0
6 1
7 0
8 0
9 0
10 0
警告: 聚合或其他 SET 操作消除了 Null 值。
(9 行受影响)
*/
with a(id,userid,content) as
(select 1,2,333 union all
select 2,3,444 union all
select 3,4,555)
select (case when userid=2 OR userid=3 then 1 when userid=4 then 2 else userid end)
as c,COUNT(*) as d from a
group by (case when userid=2 OR userid=3 then 1 when userid=4 then 2 else userid end)