如何根据条件按不同列数进行选择和分组?

Question

在我的WHERE子句中有"组";以OR分隔的选项，例如:

WHERE (group_='g1' AND unit='u1' AND department='d1')
OR (group_='g25' AND unit='u54' AND department='d70' AND team='t88')

或

WHERE (group_='g1' AND unit='u1' AND department='d1')
OR (group_='g25' AND unit='u54' AND department='d70' AND team='t88')
OR (group_='g3' AND unit='u12')

这些组中的每一个可能有不同数量的列，所以有些可以是null。这就是为什么我要SELECT和GROUP BY选定的列。

我试过了:

SELECT
CASE
WHEN team IS NULL
THEN group_, unit, department

WHEN team IS NULL AND department IS NULL
THEN group_, unit
WHEN team IS NULL AND department IS NULL AND unit IS NULL
THEN group_
ELSE
group_, unit, department, team
END
COUNT(CASE WHEN status='active' then 1 END) AS active_count,
COUNT(CASE WHEN status='inactive' then 1 END) AS inactive_count
FROM my_table
WHERE (group_='g1' AND unit='u1' AND department='d1')
OR (group_='g25' AND unit='u54' AND department='d70' AND team='t88')
OR (group_='g3' AND unit='u6')
GROUP BY
CASE
WHEN team IS NULL
THEN group_, unit, department

WHEN team IS NULL AND department IS NULL
THEN group_, unit
WHEN team IS NULL AND department IS NULL AND unit IS NULL
THEN group_
ELSE
group_, unit, department, team
END
ORDER BY group_

但这显然是错误的。演示:https://dbfiddle.uk/X7PBwchn

我想要达到的是:

我有以下数据:

<表类>id用户名组单位部门团队状态tbody><<tr>1user1g1u1d1t1活动2user2g1u1d1t2活动3user3g1u1d1t3的4user4g3u6d12t30活动5user5g25u54d70t88的

如果您通过CASE表达式将所需的NULL值修正为NULL，并设法正确地做到这一点，则可以直接应用聚合。

SELECT [group], [unit],
CASE WHEN NOT ([group] = 'g3' AND [unit] = 'u6') 
THEN [department] END AS [department],
CASE WHEN NOT ([group] = 'g3' AND [unit] = 'u6') 
AND NOT ([group] = 'g1' AND [unit] = 'u1' AND [department] = 'd1')
THEN [team] END AS [team],
SUM(CASE WHEN [status] = 'active' 
THEN 1 ELSE 0 END) AS [active_count],
SUM(CASE WHEN [status] = 'inactive' 
THEN 1 ELSE 0 END) AS [inactive_count]
FROM tab
WHERE ([group]='g1'  AND [unit]='u1'  AND [department]='d1')
OR ([group]='g25' AND [unit]='u54' AND [department]='d70' AND [team]='t88')
OR ([group]='g3'  AND [unit]='u6')
GROUP BY [group], [unit],
CASE WHEN NOT ([group] = 'g3' AND [unit] = 'u6') 
THEN [department] END,
CASE WHEN NOT ([group] = 'g3' AND [unit] = 'u6') 
AND NOT ([group] = 'g1' AND [unit] = 'u1' AND [department] = 'd1')
THEN [team] END

点击这里查看演示。

为了避免复制粘贴分组，我会创建一个包含所有所需值的交叉应用:

SELECT

gg.group_, gg.unit, gg.department, gg.team,
COUNT(CASE WHEN status='active' then 1 END) AS active_count,
COUNT(CASE WHEN status='inactive' then 1 END) AS inactive_count
FROM my_table m
cross apply (
select 1 AS groupType, group_, unit, department, null as team
where (group_='g1' AND unit='u1' AND department='d1')
union all
select 2, group_, unit, department, team
where   (group_='g25' AND unit='u54' AND department='d70' AND team='t88')
union all
select 3, group_, unit, null, null
where (group_='g3' AND unit='u6')
) gg
GROUP BY
gg.groupType, gg.group_, gg.unit, gg.department, gg.team
ORDER BY gg.group_

这可能也会简化你的代码生成