如何在SQL查询中选择所有日期(How to select all dates in SQL query)

如何在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 oi

The result is the follwoing:

2016-05-05 1562 2016-05-06 3865 2016-05-09 1 ...etc

The 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