如何在水平行中显示某个日期范围内的所有日期? [英] How to show all dates from a certain date range in horizontal row?
问题描述
我在 PostgreSQL 中有一个名为 t1
的数据库表,例如:
I have database table in PostgreSQL named as t1
like:
| Name | | StartDate | | EndDate |
----------------------------------------
| Oct-18 | | 2018-10-01| | 2018-10-05|
我想要日期范围的结果,例如:
I want the result for the date range like:
| Oct-18 | |2018-10-01| |2018-10-02| |2018-10-03| |2018-10-04| |2018-10-05|
在 generate_series()
的帮助下,我可以垂直"完成,但是如何在一行中获得结果?
with the help of generate_series()
I can do it "vertically", but how to get the result in a single row?
推荐答案
使用 generate_series()
.但是 SQL 不允许动态数量的结果列.因此,您必须将结果包装在字符串、数组或文档类型中才能使其工作.LATERAL
子查询中的 ARRAY 构造函数示例 - Postgres 10 或更高版本:
Use generate_series()
. But SQL does not allow a dynamic number of result columns. So you must wrap your result in a string, array or document type to make it work.
Example with an ARRAY constructor in a LATERAL
subquery - in Postgres 10 or later:
SELECT t1.name, d.date_arr::date[]
FROM t1
LEFT JOIN LATERAL (
SELECT ARRAY(SELECT generate_series(t1.startdate::timestamp
, t1.enddate::timestamp
, interval '1 day'))
) d(date_arr) ON true;
为什么(最好)使用 Postgres 10 或更高版本?
Why (preferably) Postgres 10 or later?
为什么要转换为 timestamp
?
为什么LEFT JOIN .. ON true
?
虽然在这种特殊情况下 LEFT JOIN
不是必需的(可能是 CROSS JOIN
),因为 ARRAY 构造函数总是返回一行.
Though LEFT JOIN
is not necessary in this particular case (could be CROSS JOIN
) because the ARRAY constructor always returns a row.
LATERAL
需要 Postgres 9.3 或更高版本.您可以替换为相关子查询:
LATERAL
requires Postgres 9.3 or later. You can substitute with a correlated subquery:
SELECT name
, ARRAY(SELECT generate_series(startdate::timestamp
, enddate::timestamp
, interval '1 day')::date)
FROM t1;
甚至适用于 pg 8.4:
Even works with pg 8.4:
db<>fiddle 这里
但考虑升级到当前版本.
But consider upgrading to a current version.
crosstab()
也无法克服 SQL 的静态特性.准备好的行类型的解决方法有限......
crosstab()
cannot overcome the static nature of SQL, either. There are limited workarounds with prepared row types ...
这篇关于如何在水平行中显示某个日期范围内的所有日期?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!