要在一系列日期上联接的sqlite查询?

创建一个日历表并加入到其中:

这样做可以做到:

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为止 在日期表的日期列中添加索引不会有任何影响。我的青石有点生锈,所以我建议你咨询一下 the manual 就这点而言。

编辑: 索引代码:

create unique index ux_dates_date on dates (date);

我想你需要这样的东西:

SELECT sum(count) / ((strftime('%s','2010-01-10')-strftime('%s','2010-01-04')+86400)/86400)
FROM sales
WHERE date IS BETWEEN '2010-01-04' AND '2010-01-10';

查询通过在给定日期之间用秒计算确切的天数。
除以86400(=24*3600)秒。

例如

SELECT (strftime('%s','2010-03-01')-strftime('%s','2010-01-01') + 86400) / 86400

输出 60 这是正确的值。

查看sqlite的 Date And Time Functions.

可以使用递归公用表表达式生成一系列日期并将其联接:

WITH RECURSIVE
  cnt(x) AS (
    SELECT 0
    UNION ALL
    SELECT x+1 FROM cnt
    LIMIT (SELECT ((julianday('2010-01-10') - julianday('2010-01-04'))) + 1)
), 
WITH date_series AS (
  SELECT date(julianday('2010-01-04'), '+' || x || ' days') as date 
FROM date_series ds
)
SELECT AVG(COALESCE(s.count, 0)) as avg_sales_count
FROM date_series ds
  LEFT JOIN sales s ON ds.date = s.date

我写了一篇博文,上面有更多信息: http://www.geekytidbits.com/generate-date-range-sqlite/