时间戳

首页 > 解决方案 > 将 2 个 sql 查询合并为 1 个查询

mysql - 将 2 个 sql 查询合并为 1 个查询

问题描述

我现在有 2 个查询，如果可能的话，我希望将它们合并为 1 个。

我有存储在Tickets_Open 表中的开放票和Tickets_Closed 中的已关闭票。两个表都有“Date_Requested”和“Date_Completed”列。我需要计算每天请求和完成的门票数量。

我的门票请求计数查询如下：

SELECT SUM(Count) AS TotalOpen, Date FROM(
 SELECT COUNT(Ticket_Request_Code) AS Count, Date_Requested AS Date
 FROM Tickets_Closed
 WHERE Date_Requested >='2018-01-01 00:00:00'
 GROUP BY(Date_Requested)
 UNION
 SELECT COUNT(Work_Request_Code) AS Count, Date_Requested AS Date
 FROM Tickets_Open
 WHERE Date_Requested >='2018-01-01 00:00:00'
 GROUP BY(Date_Requested)
) AS t1 GROUP BY Date ORDER BY `t1`.`Date` DESC

我的门票完成计数查询如下：

SELECT COUNT(Ticket_Request_Code) AS CountClosed, Date_Completed AS Date
FROM Tickets_Closed
Where Date_Completed >='2018-01-01 00:00:00'
GROUP BY(Date_Completed)

两个查询都返回正确的结果。对于打开，它返回列标题 Date 和 TotalOpen。对于关闭，它返回列标题 Date 和 CountClosed。

是否可以使用以下列标题 Date、TotalOpen、CountClosed 返回它。

标签： mysqlsql

解决方案

您可以将它们组合为：

SELECT Date, SUM(isopen) as isopen, SUM(isclose) as isclose
FROM ((SELECT date_requested as date, 1 as isopen, 0 as isclose
       FROM Tickets_Closed
       WHERE Date_Requested >= '2018-01-01'
      ) UNION ALL
      (SELECT date_requested, 1 as isopen, 0 as isclose
       FROM Tickets_Open
       WHERE Date_Requested >= '2018-01-01'
      ) UNION ALL
      (SELECT date_closed as date, 0 as isopen, 1 as isclose
       FROM Tickets_Closed
       WHERE date_closed >= '2018-01-01'
      )
     ) t
GROUP BY Date
ORDER BY Date DESC;

这假定Ticket_Request_Code并且Work_Request_Code不是NULL。如果COUNT()确实用于检查NULL值，则将条件添加到WHERE每个子查询的子句中。

推荐阅读