sql - 选择日期范围之间的计数

Question

我想计算 2 个日期之间的所有条目(包括上周到今天)，如果没有，请选择 0。目前它打印如下:

+-------+------------+
| items | SellDate   |
+-------+------------+
| 1     | 2017-01-01 |
+-------+------------+
| 3     | 2017-01-02 |
+-------+------------+
| 1     | 2017-01-03 |
+-------+------------+
| 5     | 2017-01-06 |
+-------+------------+

但是，我需要这样打印的东西:

+-------+------------+
| items | SellDate   |
+-------+------------+
| 1     | 2017-01-01 |
+-------+------------+
| 3     | 2017-01-02 |
+-------+------------+
| 1     | 2017-01-03 |
+-------+------------+
| 0     | 2017-01-04 |
+-------+------------+
| 0     | 2017-01-05 |
+-------+------------+
| 5     | 2017-01-06 |
+-------+------------+
| 0     | 2017-01-07 |
+-------+------------+

我的查询如下所示:

SELECT 
    COUNT(Item.id) AS Items,
    DATE(Item.sold_at) AS SellDate
FROM Item
WHERE Item.sold_at IS NOT NULL AND Item.sold_at BETWEEN DATE(DATETIME('now', 'localtime', '-6 days')) AND DATE(DATETIME('now', 'localtime', '+1 day'))
GROUP BY SellDate

我做错了什么？

Answer 1

据我所知，如果没有 recursive common table expression，这是不可能的SQLite 3.8.3 及更高版本支持。使用相应的版本，您可以通过将日期范围与项目列表连接来实现:

WITH RECURSIVE dates(date) AS (
  VALUES(DATE(DATETIME('now', 'localtime', '-6 days')))
  UNION ALL
  SELECT date(date, '+1 day')
  FROM dates
  WHERE date < DATE(DATETIME('now', 'localtime', '+1 day'))
)
SELECT 
    date,
    COUNT(Item.id) AS Items
FROM 
    dates
LEFT JOIN
    Item
ON
    dates.date = Item.SellDate
GROUP BY
    SellDate