SQL 合并多个 SELECT 语句

Question

我正在尝试构建一个 SQLite 查询，以从单个表中收集统计信息。

该表保存了某种日志，每天有多个条目。我需要在搜索参数内为每一天获取一个单独的行，然后编译那些日期内具有特定布尔值的行的总数。

以下是我目前拥有的查询：

SELECT DATE(DateTime) AS SearchDate,
     (SELECT COUNT() AS Total
                FROM CallRecords
                WHERE DATE(DateTime)
                BETWEEN '2017-08-27' AND '2017-09-02'
                GROUP BY DATE(DateTime)
                ORDER BY Total DESC) AS Total,
    (SELECT COUNT() AS Total
                FROM CallRecords
                WHERE NoMarket = 1
                AND DATE(DateTime)
                BETWEEN '2017-08-27' AND '2017-09-02'
                GROUP BY DATE(DateTime)
                ORDER BY Total DESC) AS NoMarkets,
    (SELECT COUNT() AS Total
                FROM CallRecords
                WHERE Complaint = 1
                AND DATE(DateTime)
                BETWEEN '2017-08-27' AND '2017-09-02'
                GROUP BY DATE(DateTime)
                ORDER BY Total DESC) AS Complaints,
    (SELECT COUNT() AS Total
                FROM CallRecords
                WHERE Voicemail = 1
                AND DATE(DateTime)
                BETWEEN '2017-08-27' AND '2017-09-02'
                GROUP BY DATE(DateTime)
                ORDER BY Total DESC) AS Voicemails
FROM CallRecords
WHERE DATE(DateTime) BETWEEN '2017-08-27' AND '2017-09-02'
GROUP BY SearchDate

输出结果：

8/28/2017   175 27      11
8/29/2017   175 27      11
8/30/2017   175 27      11
8/31/2017   175 27      11
9/1/2017    175 27      11

您可以看到，它正确地获取了每个单独的日期，但列的总计不正确。

显然，在我的查询中我漏掉了一些东西，但我不确定是哪里。有没有更好的方法执行这个查询？

编辑：我已经研究了几个其他问题，标题几乎相同，但我没有找到任何类似于我想要的内容。大多数似乎比我要完成的任务更加复杂。

- Zephyr

---

请提供一个 [mcve]。https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query - Yunnosch

@Yunnosch，我怎么能在不提供实际数据库的情况下完成这个任务呢？ - Zephyr

通过为适当定制的玩具数据库提供SQLite命令行工具的.dump文件。 - Yunnosch

Answer 1

看起来你的CallRecords表中有一堆列，像Complaint和Voicemail，每个列都对应一次通话。

看起来这些列在有关时的值为1。

因此，这个查询可能会对你有所帮助。

SELECT DATE(DateTime) AS SearchDate,
       COUNT(*) AS Total,
       SUM(NoMarket = 1) AS NoMarkets,
       SUM(Complaint = 1) AS Complaints,
       SUM(Voicemail = 1) AS Voicemails
  FROM CallRecords
 WHERE DateTime >=  '2017-08-27'
   AND DateTime <   '2017-09-02' + INTERVAL 1 DAY
 GROUP BY DATE(DateTime)

为什么这个方法有效呢？因为在MySQL中，像 Voicemail = 1 这样的布尔表达式，在真时的值是 1 ，而在假时的值是 0。您可以很好地将这些值相加。

为什么这比你目前的方法更快呢？因为 DATE(DateTime) BETWEEN this AND that 无法利用索引上的 DateTime。

为什么对于日期范围的结束是正确的呢？因为DateTime < '2017-09-02' + INTERVAL 1 DAY会把所有记录拉进来，但不包括您的日期范围后一天午夜之前的记录。

如果您使用Sqlite，请使用AND DateTime < date('2017-09-02', '+1 day')。在那里，+ INTERVAL 1 DAY 的内容略有不同。

- O. Jones

---

“BETWEEN”不是过滤日期范围的最佳选择，因为它在日期范围的末尾可能会出现偏差。 - O. Jones

太棒了。我不知道为什么一直想把它搞得那么复杂。不过，我正在使用SQLite，而不是MySQL，所以无法使用“INTERVAL”部分。我将从调用应用程序中的查询中调整日期。谢谢！ - Zephyr

Answer 2

你可以像这样操作，尽管我是在 SQL Server 中编写的。

SELECT DATE(DateTime) AS SearchDate,
    COUNT() AS TOTAL,
    SUM(CASE WHEN NoMarket = 1 THEN 1 ELSE 0 END) AS NoMarkets,
    SUM(CASE WHEN Complaint = 1 THEN 1 ELSE 0 END) AS Complaints,
    SUM(CASE WHEN Voicemail = 1 THEN 1 ELSE 0 END) AS Voicemails
FROM CallRecords
WHERE DATE(DateTime) BETWEEN '2017-08-27' AND '2017-09-02'
GROUP BY SearchDate

- Bijan Ghasemi

Answer 3

SELECT DATE(DateTime) AS SearchDate, Total, NoMarkets, Complaints, Voicemails FROM
 (SELECT COUNT() AS Total FROM CallRecords) CR
 JOIN
(SELECT COUNT() AS NoMarkets FROM CallRecords WHERE NoMarket = 1) NM
 ON CR.DateTime = NM.DateTime
 JOIN
(SELECT COUNT() AS Complaints FROM CallRecords WHERE Complaint = 1) C
 ON NM.DateTime = C.DateTime
 JOIN
(SELECT COUNT() AS Voicemails FROM CallRecords WHERE Voicemail = 1) VM
 ON C.DateTime = VM.DateTime
 JOIN CallRecords CLR ON VM.DateTime=CLR.DateTime WHERE DATE(CLR.DateTime) >= '2017-08-27' AND DATE(CLR.DateTime) <= '2017-09-02'GROUP BY SearchDate;

这可能会正确输出。

- Jignesh M. Khatri

---

名为 NM 的虚拟表没有 DateTime 列。其他表也是如此。因此，JOIN 操作无法正常工作。 - O. Jones

那么如何按日期将记录存储在数据库表中？ - Jignesh M. Khatri