mysql - 如何在mysql中的特定日期范围内进行计数

Question

我创建了这个 mysql 代码以仅在特定日期范围内计数，我转储了我的 mysql 代码的输出，它是:

SELECT COUNT(
                IF(
                        DATE(q.date_emailed) BETWEEN '2014-02-01' AND '2014-02-28',
                        1,
                        0
                    )
        ) AS 'Feb', 
        COUNT(
                    IF(
                        DATE(q.date_emailed) BETWEEN '2014-03-01' AND '2014-03-31',
                        1,
                        0
                    )
         ) AS 'March', 
         COUNT(
                    IF(
                        DATE(q.date_emailed) BETWEEN '2014-01-01' AND '2014-01-31',
                        1,
                        0
                    )
         ) AS 'Jan' 
        FROM
            database.quotes q
        WHERE (DATE(q.date_emailed) BETWEEN '2014-01-01' AND '2014-03-31')

但这每个月输出相同的计数，其中我确认 2 月和 3 月的计数为零。我在这里错过了什么？

Answer 1

COUNT() 计算非空值。您可以改用 sum() 来修复您的代码。您还可以通过删除 if 语句来简化它:

SELECT SUM(DATE(q.date_emailed) BETWEEN '2014-02-01' AND '2014-02-28') AS Feb, 
       SUM(DATE(q.date_emailed) BETWEEN '2014-03-01' AND '2014-03-31') AS March, 
       SUM(DATE(q.date_emailed) BETWEEN '2014-01-01' AND '2014-01-31') AS Jan
FROM database.quotes q
WHERE DATE(q.date_emailed) BETWEEN '2014-01-01' AND '2014-03-31';

在 MySQL 中， bool 结果被视为 0 表示“false”，1 表示“true”。这非常方便，您可以使用 sum() 来计算匹配项的数量。

请注意，我还删除了列名称周围的单引号。单引号应该用于字符串和日期常量，而不是标识符。

编辑:

您可以通过在 q.date_emailed 上使用索引来加快此查询的运行速度。但是，我认为不会因为 date() 函数而使用索引。您可以通过更改逻辑来解决此问题:

SELECT SUM(DATE(q.date_emailed) BETWEEN '2014-02-01' AND '2014-02-28') AS Feb, 
       SUM(DATE(q.date_emailed) BETWEEN '2014-03-01' AND '2014-03-31') AS March, 
       SUM(DATE(q.date_emailed) BETWEEN '2014-01-01' AND '2014-01-31') AS Jan
FROM database.quotes q
WHERE q.date_emailed >= '2014-01-01' AND
      q.date_emailed < '2014-04-01';