MySQL获取绘制条形图的日期

Question

我正在尝试使用类似以下查询的内容从数据库中提取给定日期范围内的所有信息:

SELECT transactionDate, SUM(transactionTotal) 
  FROM transaction
 WHERE transactionDate BETWEEN '2014-06-01' AND '2014-08-11'
  AND transactionType = 'end'
GROUP BY DAYOFMONTH(transactionDate)
ORDER BY transactionDate ASC

但不知何故我只获得了 1 个月的数据

2014-06-01 20:38:05 4811500.00
2014-06-02 20:42:59 5924950.00
2014-06-03 20:44:38 3811500.00
2014-06-04 11:45:13 4472000.00
2014-06-05 15:34:53 7922000.00
2014-06-06 17:45:28 5027000.00
2014-06-07 11:25:38 4378000.00
2014-06-08 07:59:04 4250000.00
2014-06-09 08:41:49 4766500.00
2014-06-10 01:23:35 4071000.00
2014-06-11 01:01:30 1459000.00
2014-06-12 15:05:08 2960000.00
2014-06-13 00:47:09 1160000.00
2014-06-14 16:52:20 4208000.00
2014-06-16 00:05:18 3947500.00
2014-06-17 00:18:39 4926000.00
2014-06-18 00:33:38 4244500.00
2014-06-19 00:43:39 4045000.00
2014-06-20 22:47:54 2649500.00
2014-06-21 23:06:04 4030000.00
2014-06-22 23:19:22 945500.00
2014-06-23 23:29:27 3015000.00
2014-06-24 23:35:56 2420000.00
2014-06-25 00:02:03 3920000.00
2014-06-26 00:50:33 4841000.00
2014-06-27 10:39:14 4095000.00
2014-06-28 07:43:06 5605500.00
2014-06-29 11:48:24 1939000.00
2014-06-30 10:49:50 3620000.00

如您所见，即使所有其他日期也有要显示的数据，我也获得了从 2014 年 6 月 1 日到 2014 年 6 月 1 日的结果。

希望您能帮助我，谢谢!

Answer 1

您滥用了GROUP BY和有害的 MySQL GROUP BY扩展正在咬你。您将使用 GROUP BY DAYOFMONTH() 将数据分组到 31 个存储桶中，因此您的结果集中不可能获得超过 31 行的数据。然后(滥用扩展名)显示一些任意选择的 transaction_date 31 个分组中每个分组的值(value)。恰好是六月的日期，这让您误以为您只选择了六月的记录。您实际上已选择了范围内的所有日期，但错误地将 7 月和 8 月记录与 6 月记录分组。哎哟。

其次，使用BETWEEN从DATETIME中选择日期范围列不太正确，因为它得到了错误的范围末尾。 BETWEEN '2014-06-01' AND '2014-08-11'获取日期从 6 月 1 日到 8 月 11 日凌晨的项目。因此 8 月 11 日中午发生的事情不会被选中。

这是正确完成这项工作所需的查询。

    SELECT DATE(transactionDate),
           SUM(transactionTotal) 
      FROM transaction 
     WHERE transactionDate >= '2014-06-01'
       AND transactionDate  < '2014-08-11' + INTERVAL 1 DAY
       AND transactionType = 'end'
  GROUP BY DATE(transactionDate) 
  ORDER BY DATE(transactionDate) ASC

这是怎么回事？首先，我们仅按每笔交易的日期进行分组，使用 DATE() ，我们在结果集中包含该分组值 SELECT DATE() .

其次，日期范围选择涵盖 6 月 1 日午夜或之后的所有内容，直到 8 月 12 日午夜之前的那一刻。这就是 transactionDate < '2014-08-11' + INTERVAL 1 DAY 的要点。需要 8 月 11 日一整天的元素。

这里有一篇有关 SQL 技术的文章:http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/