mysql - 结合多个选择查询问题

Question

MySql查询问题:

作为 sql 的新手，我有以下问题。我很难将 4 个查询合并为一个用于报告软件。代码有效，但结果不是我想要的。

尝试使用 UNION 语句，这会显示计数，但全部排序在 1 个“count(*)”字段下。

我想在每个查询中包含 4 个字段(警报总数、确认总数、15 分钟内确认总数、15 分钟后确认总数)

到目前为止，这是我的代码:

select
  count(*) 'Total alarms'
 FROM  alarminfo
 where
 ((alarminfo.set_time>='2017-09-01 00:00:00.0' AND alarminfo.set_time<'2017-09-15 23:59:59.0'))

union 

select
 count(ack_time) 'Total Acknowledged'
 FROM  alarminfo
  where
 ((alarminfo.set_time>='2017-09-01 00:00:00.0' AND alarminfo.set_time<'2017-09-15 23:59:59.0')

 )

union 

select
   count(*) 'Total acknowledged within 15min'
 FROM  alarminfo
  where
 ((alarminfo.set_time>='2017-09-01 00:00:00.0' AND alarminfo.set_time<'2017-09-15 23:59:59.0') AND
    (TIMESTAMPDIFF(SECOND,alarminfo.set_time ,alarminfo.ack_time)<60*15))

union 

select
   count(*) 'Total acknowledged after 15min'
 FROM  alarminfo
  where
 ((alarminfo.set_time>='2017-09-01 00:00:00.0' AND alarminfo.set_time<'2017-09-15 23:59:59.0') AND
    (TIMESTAMPDIFF(SECOND,alarminfo.set_time ,alarminfo.ack_time)>60*15))

有人知道如何在单独的字段下对每个计数进行排序吗？

Answer 1

我认为您需要条件聚合。例如

select
  count(*) 'Total alarms',
  sum(case when ack_time is not null then 1 else 0 end) as 'total acknowledged',
  sum(case when (TIMESTAMPDIFF(SECOND,alarminfo.set_time ,alarminfo.ack_time)<60*15) then 1 else 0 end) as 'Total acknowledged within 15min',
  sum(case when (TIMESTAMPDIFF(SECOND,alarminfo.set_time ,alarminfo.ack_time)>60*15) then 1 else 0 end) as 'Total acknowledged after 15min'
 FROM  alarminfo
 where
 alarminfo.set_time>='2017-09-01 00:00:00.0' AND alarminfo.set_time<'2017-09-15 23:59:59.0'