SQL Server 如何将并集中的每个组组合成单行？

des4xlb0 于 2023-01-20 发布在其他

关注(0)|答案(2)|浏览(147)

给定以下示例数据：
| 身份证|用户名|群|单位|部门|团队|地位|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 1个|用户1|g1|u1|第1天|t1|主动|
| 第二章|用户2|g1|u1|第1天|t2|主动|
| 三个|用户3|g1|u1|第1天|三度|不活跃的|
| 四个|用户4|三代|u6|十二日|t30|主动|
| 五个|用户5|二十五国集团|u54|d70|t88|不活跃的|
下面的查询按每个选定组/单位/部门/团队的active和inactive对结果进行分组，例如：

SELECT group
     , unit
     , department
     , NULL as team
     , COUNT(*) AS count
  FROM table
 WHERE group='g1' 
   AND unit='u1' 
   AND department='d1'
GROUP
    BY group
     , unit
     , department
UNION ALL
SELECT group
     , unit
     , department
     , team
     , COUNT(*) AS count
  FROM table
 WHERE group='g25' 
   AND unit='u54' 
   AND department='d70' 
   AND team='t88'
GROUP
    BY group
     , unit
     , department
     , team

这导致以下结果：
| 群|单位|部门|团队|地位|计数|
| - ------|- ------|- ------|- ------|- ------|- ------|
| g1|u1|第1天|零|主动|第二章|
| g1|u1|第1天|零|不活跃的|1个|
| 二十五国集团|u54|d70|t88|主动|无|
| 二十五国集团|u54|d70|t88|不活跃的|1个|
是否可以将每个组变成一行，即：
| 群|单位|部门|团队|活动计数|非活动计数|
| - ------|- ------|- ------|- ------|- ------|- ------|
| g1|u1|第1天|零|第二章|1个|
| 二十五国集团|u54|d70|t88|1个|无|
编辑：我需要基于收到的数组构建查询，例如，我可以获得以下数组：
["g1", "u1", "d1"]和["g25", "u54", "d70", "t88"]和["g3", "u6"]。（您可以假设对于每个数组，您都获得了该级别的列，因此，例如，如果有“unit”，则也会有“group”）。那么查询将为：

SELECT group_
     , unit
     , department
     , NULL as 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
 WHERE group_='g1' 
   AND unit='u1' 
   AND department='d1'
group
    BY group_
     , unit
     , department
UNION ALL
SELECT group_
     , unit
     , department
     , 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
 WHERE group_='g25' 
   AND unit='u54' 
   AND department='d70' 
   AND team='t88'
group
    BY group_
     , unit
     , department
     , team
UNION ALL
SELECT group_
     , unit
     , NULL as department
     , NULL as 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
 WHERE group_='g3' 
   AND unit='u6' 
group
    BY group_
     , unit

演示：https://dbfiddle.uk/Nv2csSc8

sql-server

来源：https://stackoverflow.com/questions/75145264/how-to-combine-each-group-in-the-union-into-single-row

2条答案

按热度按时间

doinxwow1#

您可以使用条件计数函数代替count，如下所示：

count(case when status='active' then 1 end) as active_count,
count(case when status='inactive' then 1 end) as inactive_count

根据所提供的逻辑，我认为您可以在没有联合的情况下重建查询，如下所示：

select group_, unit, department, 
       case when group_='g1' then null else team end as team,
       count(case when status='active' then 1 end) as active_count,
       count(case when status='inactive' then 1 end) as inactive_count
from table_name
where (group_='g1' and unit='u1' and department='d1') or
      (group_='g25' and unit='u54' and department='d70' and team='t88')
group by group_, unit , department,
         case when group_='g1' then null else team end
order by group_

See demo

赞(0）回复(0）举报 2023-01-20

hmmo2u0o2#

我保留了您的解决方案，并使用条件计数状态对其进行了一些更新：

SELECT _group, unit, department, NULL as team,
count(case when status='active' then 1 end) as active_count,
count(case when status='inactive' then 1 end) as inactive_count
FROM _table
WHERE _group='g1' AND unit='u1' AND department='d1'
GROUP BY _group, unit, department
UNION ALL
SELECT _group, unit, department, team,
count(case when status='active' then 1 end) as active_count,
count(case when status='inactive' then 1 end) as inactive_count
FROM _table
WHERE _group='g25' AND unit='u54' AND department='d70' AND team='t88'
GROUP BY _group, unit, department, team

此处演示：https://dbfiddle.uk/OBD2Xob3

赞(0）回复(0）举报 2023-01-20

我来回答

SQL Server 如何将并集中的每个组组合成单行？

2条答案

相关问题

热门标签

最新问答