如何在SQL查询中选择所有日期(How to select all dates in SQL query)
SELECT oi.created_at, count(oi.id_order_item)
FROM order_item oi
结果如下:
2016-05-05 1562 2016-05-06 3865 2016-05-09 1 ...etc问题是我需要所有日子的信息,即使这个日期没有id_order_item。
预期结果:
Date Quantity 2016-05-05 1562 2016-05-06 3865 2016-05-07 0 2016-05-08 0 2016-05-09 1 SELECT oi.created_at, count(oi.id_order_item) FROM order_item oiThe result is the follwoing:
2016-05-05 1562 2016-05-06 3865 2016-05-09 1 ...etcThe problem is that I need information for all days even if there were no id_order_item for this date.
Expected result:
Date Quantity 2016-05-05 1562 2016-05-06 3865 2016-05-07 0 2016-05-08 0 2016-05-09 1最满意答案
你无法计算数据库中没有的东西。 因此,您需要生成缺少的日期才能“计算”它们。
SELECT d.dt, count(oi.id_order_item) FROM ( select dt::date from generate_series( (select min(created_at) from order_item), (select max(created_at) from order_item), interval '1' day) as x (dt) ) d left join order_item oi on oi.created_at = d.dt group by d.dt order by d.dt;查询从现有订单商品中获取最小和最大日期。
如果您想要特定日期范围的计数,可以删除子选择:
SELECT d.dt, count(oi.id_order_item) FROM ( select dt::date from generate_series(date '2016-05-01', date '2016-05-31', interval '1' day) as x (dt) ) d left join order_item oi on oi.created_at = d.dt group by d.dt order by d.dt;SQLFiddle: http ://sqlfiddle.com/#!15/49024/5
You can't count something that is not in the database. So you need to generate the missing dates in order to be able to "count" them.
SELECT d.dt, count(oi.id_order_item) FROM ( select dt::date from generate_series( (select min(created_at) from order_item), (select max(created_at) from order_item), interval '1' day) as x (dt) ) d left join order_item oi on oi.created_at = d.dt group by d.dt order by d.dt;The query gets the minimum and maximum date form the existing order items.
If you want the count for a specific date range you can remove the sub-selects:
SELECT d.dt, count(oi.id_order_item) FROM ( select dt::date from generate_series(date '2016-05-01', date '2016-05-31', interval '1' day) as x (dt) ) d left join order_item oi on oi.created_at = d.dt group by d.dt order by d.dt;SQLFiddle: http://sqlfiddle.com/#!15/49024/5
更多推荐
发布评论