通过此查询,我可以获得上周创建的所有条目:
SELECT day, COALESCE(ct, 0) AS ct
FROM (SELECT now::date - d AS day FROM generate_series (0, 6) d) d -- 6, not 7
LEFT JOIN (
SELECT created_at::date AS day, count(*) AS ct
FROM entries
WHERE created_at >= date_trunc('day', now()) - interval '6d'
GROUP BY 1
) e USING (day);
它返回如下结果:
count | date
2 | 15.01.2014
0 | 14.01.2014
1 | 13.01.2014
0 | 12.01.2014
0 | 11.01.2014
0 | 10.01.2014
9 | 09.01.2014
现在我还想显示上周所有删除的条目!我可以带他们穿过田野deleted_at
: 我尝试的是:
SELECT day, COALESCE(ct, 0) AS created, COALESCE(dl,0) AS deleted
FROM (SELECT current_date - d AS day FROM generate_series (0, 6) d) d
LEFT JOIN (
SELECT created_at::date AS day,
count(
CASE WHEN (created_at >= date_trunc('day', now()) - interval '6d') THEN 1 ELSE 0 END
) AS ct,
count(
CASE WHEN (canceled_at >= date_trunc('day', now()) - interval '6d') THEN 1 ELSE 0 END
) AS dl
FROM entries
GROUP BY 1
) e USING (day);
但这没有用!现在我得到两行相同的行:
deleted | created | date
2 | 2 | 15.01.2014
0 | 0 | 14.01.2014
1 | 1 | 13.01.2014
0 | 0 | 12.01.2014
0 | 0 | 11.01.2014
0 | 0 | 10.01.2014
9 | 9 | 09.01.2014
我有什么错吗?如何显示已创建和已删除的条目?