SQLite查询要加入一定范围的日期？

Question

我正在使用SQLite。

假设我有一个sales带有两列的表格，date和count，以跟踪该日期我卖出了几杯柠檬水。如果我在某一天不卖柠檬水，那我会很沮丧以至于无法在sales表格中创建新的一行。

我想找出在给定日期范围内售出的平均眼镜数量。我可以发出这样的查询：

SELECT AVG(count) FROM sales WHERE date IS BETWEEN '2010-01-04' AND '2010-01-10';

但是，除非表中包含该范围内的所有日期，否则结果将不准确。我需要某种方法告诉SQLite将丢失的行计为零。

我想如果可以在某个日期范围内加入销售表，那将是理想的。这似乎是不可能的，因此从日期范围创建表的简单方法可能是下一个最好的方法。我不想修改销售表，这对于柠檬水摊来说似乎很愚蠢，但在现实生活中更有意义。

就像我在上面说的，我正在使用SQLite，因此如果您向我推荐其他数据库，对您没有帮助。就是说，如果您对其他数据库有更多的经验，并且认为您的解决方案可以在普通SQL中完成，那么我很乐意听到。

Answer 1

创建一个日历表并加入该表：

这样的事情会做：

create table dates (id integer primary key);
insert into dates default values;
insert into dates default values;
insert into dates select null from dates d1, dates d2, dates d3 , dates d4;
insert into dates select null from dates d1, dates d2, dates d3 , dates d4;
alter table dates add date datetime;
update dates set date=date('2000-01-01',(-1+id)||' day');

直到2287-05-20为止，您都可以使用。在dates表的date列中添加索引不会有任何问题。我的sqlite有点生锈，因此建议您参考该手册。

编辑： 索引代码：

create unique index ux_dates_date on dates (date);