SQL组合多个SELECT语句

我正在尝试构建一个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

正如您所看到的,它正确地获取每个日期,但列的总数不正确.

显然,我在查询中遗漏了一些内容,但我不知道在哪里.有没有更好的方法来执行此查询？

编辑:我已经在这里查看了几个其他几乎相同标题的问题,但我没有发现任何类似于我正在寻找的东西.大多数似乎比我想要完成的要复杂得多.

看起来你的表中有一堆乱七八糟的列,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具有值为1true 0时的值以及何时为false.你可以很好地总结这些值.

为什么它比你拥有的更快？因为DATE(DateTime) BETWEEN this AND that无法利用索引DateTime.

为什么在日期范围结束时它是正确的？因为 在您的日期范围之后DateTime <'2017-09-02' + INTERVAL 1 DAY的第二天,所有记录都会被拉到,但不包括午夜.

如果您正在使用Sqlite,则需要AND DateTime .+ INTERVAL 1 DAY那里的东西略有不同.