关于SQL分组求和的问题，如何解决？

groupby相关代码优化

(select count(1) from em_order em where em.type_id in ('业务') )as business,
(select count(1) from em_order em where em.type_id in ('故障') )as fault,
(select count(1) from em_order em where em.type_id in ('计划') )as plan,
(select count(1) from em_order em where em.type_id in ('配合') )as engin,
(select count(1) from em_order em where em.type_id in ('任务') )as task,
(select count(1) from em_order em where em.type_id in ('支撑') )as support,
(select count(1) from em_order em where em.type_id in ('管理') )as resource,
(select count(1) from em_order em where em.type_id in ('操作') )as risk,
(select count(1) from em_order em where em.is_overtime in ('是') )as overtime,
(select count(1) from em_order em where em.is_overtime in ('否') and now() > em.alarm_time )as timeout,
(select count(1) from em_order em where em.status_id in ('建态','派单','接单') )as notake,
(select count(1) from em_order em where em.status_id in ('执行','回单','竣工','归档','挂起','退单') )as take

运行结果正确

优化代码，均为同一表，大部分为同一字段不同状态求和并赋别名返给前端

类似SELECT count(1) from em_order eo group by type_id;一句SQL语句查询得到结果，而非十余句

要求改为这种结构
[
{
"name": "故障工单"
"count": 32
},
{
"name": "业务开通"
"count": 32
},
{
"name": "工程配合"
"count": 32
}

]
返回List<Map<String, Object>>

SELECT COUNT(CASE WHEN em.type_id ='业务'  THEN 1 ELSE NULL END ) AS business
      ,COUNT(CASE WHEN em.type_id ='故障'  THEN 1 ELSE NULL END ) AS fault
      ,COUNT(CASE WHEN em.type_id ='计划'  THEN 1 ELSE NULL END ) AS plan
      ,COUNT(CASE WHEN em.type_id ='配合'  THEN 1 ELSE NULL END ) AS engin
      ,COUNT(CASE WHEN em.type_id ='任务'  THEN 1 ELSE NULL END ) AS task
      ,COUNT(CASE WHEN em.type_id ='支撑'  THEN 1 ELSE NULL END ) AS support
      ,COUNT(CASE WHEN em.type_id ='管理'  THEN 1 ELSE NULL END ) AS resource
      ,COUNT(CASE WHEN em.type_id ='操作'  THEN 1 ELSE NULL END ) AS risk
      ,COUNT(CASE WHEN em.is_overtime ='是' THEN 1 ELSE NULL END ) AS overtime
      ,COUNT(CASE WHEN em.is_overtime i= '否'  and now() > em.alarm_time THEN 1 ELSE NULL END ) AS timeout
      ,COUNT(CASE WHEN em.status_id in ('建态','派单','接单')  THEN 1 ELSE NULL END ) AS notake
      ,COUNT(CASE WHEN em.status_id in ('执行','回单','竣工','归档','挂起','退单')  THEN 1 ELSE NULL END ) AS take
FROM em_order em

参考：https://blog.csdn.net/qq_34972627/article/details/123051450

你这个需求不需要用group by,直接用count + 条件的方式就可以了。
采用下面的方式就可以一次性把你要的结果都输出。

select 
    count(case when em.type_id in ('业务') then 1 end) as business,
    count(case when em.type_id in ('故障') then 1 end) as fault,
    count(case when em.type_id in ('计划') then 1 end) as plan,
    count(case when em.type_id in ('配合') then 1 end) as engin,
    count(case when em.type_id in ('任务') then 1 end) as task,
    count(case when em.type_id in ('支撑') then 1 end) as support,
    count(case when em.type_id in ('管理') then 1 end) as resource,
    count(case when em.type_id in ('操作') then 1 end) as risk,
    count(case when em.is_overtime in ('是') then 1 end) as overtime,
    count(case when em.is_overtime in ('否') and now() > em.alarm_time then 1 end) as timeout,
    count(case when em.status_id in ('建态','派单','接单') then 1 end) as notake,
    count(case when em.status_id in ('执行','回单','竣工','归档','挂起','退单') then 1 end) as take
from em_order em



select '业务' as name ,count(1) as count from em_order em where em.type_id in ('业务') 
union all
select '故障' as name ,count(1) as count from em_order em where em.type_id in ('故障') 
union all
select '计划' as name, count(1) as count from em_order em where em.type_id in ('计划')
union all
select '配合' as name,count(1) as count from em_order em where em.type_id in ('配合') 
union all
select '任务' as name,count(1) as count from em_order em where em.type_id in ('任务') 
union all
select '支撑' as name,count(1) as count from em_order em where em.type_id in ('支撑') 
union all
select '管理' as name,count(1) as count from em_order em where em.type_id in ('管理') 
union all
select '操作' as name,count(1) as count from em_order em where em.type_id in ('操作') 
union all
select '是' as name,count(1) as count from em_order em where em.is_overtime in ('是') 
union all
select '否' as name,count(1) as count from em_order em where em.is_overtime in ('否') and now() > em.alarm_time 
union all
select '其他1' as name,count(1) as count from em_order em where em.status_id in ('建态','派单','接单') 
union all
select '其他2' as name,count(1) as count from em_order em where em.status_id in ('执行','回单','竣工','归档','挂起','退单')

同一张表得不同筛选条件用case when then else end 这个函数

这样就可以了：

select em.type_id as name ,count(1) as count from em_order em where em.type_id in ('业务','故障','计划','配合','任务','支撑','管理','操作')  group by em.type_id 
union all 
select em.is_overtime as name,count(1) as count from em_order em where em.is_overtime in ('是','否') group by em.is_overtime 
union all 
select em.status_id as name,count(1) as count from em_order em where em.status_id in ('建态','派单','接单','执行','回单','竣工','归档','挂起','退单') group by em.status_id

groupby相关代码优化

运行结果正确

优化代码，均为同一表，大部分为同一字段不同状态求和并赋别名返给前端

类似SELECT count(1) from em_order eo group by type_id;一句SQL语句查询得到结果，而非十余句

要求改为这种结构
[
{
"name": "故障工单",
"count": 32
},
{
"name": "业务开通",
"count": 32
},
{
"name": "工程配合",
"count": 32
},
...
]
返回List<Map<String, Object>>

--MSSQL
select 
    sum(case when em.type_id in ('业务') then 1 else 0 end) as business,
    sum(case when em.type_id in ('故障') then 1 else 0 end) as fault,
    sum(case when em.type_id in ('计划') then 1 else 0 end) as [plan],
    sum(case when em.type_id in ('配合') then 1 else 0 end) as engin,
    sum(case when em.type_id in ('任务') then 1 else 0 end) as task,
    sum(case when em.type_id in ('支撑') then 1 else 0 end) as support,
    sum(case when em.type_id in ('管理') then 1 else 0 end) as [resource],
    sum(case when em.type_id in ('操作') then 1 else 0 end) as risk,
    sum(case when em.is_overtime in ('是') then 1 else 0 end) as overtime,
    sum(case when em.is_overtime in ('否') and getdate() > em.alarm_time then 1 else 0 end) as timeout,
    sum(case when em.status_id in ('建态','派单','接单') then 1 else 0 end) as notake,
    sum(case when em.status_id in ('执行','回单','竣工','归档','挂起','退单') then 1 else 0 end) as take
from em_order as em