gpt4 book ai didi

带有索引标记的行的 postgresql 副本

转载 作者:行者123 更新时间:2023-11-29 13:35:29 26 4
gpt4 key购买 nike

有没有办法让sql查询返回拆分行。我什至不知道怎么问。下面有'index_tag'。

select event.name, tb_ev.start_time, tb_ev.end_time
from block as tb_ev,event
where location_id in ( 28522 )
and tb_ev.event_id=event.id;

name | start_time | end_time
---------------+------------+----------
Register #1 | 06:00:00 | 09:00:00
Register #2 | 11:00:00 | 12:00:00
Register #3 | 11:00:00 | 14:00:00

从3行到7行

 name          | start_time | end_time | index_tag
---------------+------------+--------------------
Register #1 | 06:00:00 | 09:00:00 | 06:00:00
Register #1 | 06:00:00 | 09:00:00 | 07:00:00
Register #1 | 06:00:00 | 09:00:00 | 08:00:00
Register #2 | 11:00:00 | 12:00:00 | 11:00:00
Register #3 | 11:00:00 | 14:00:00 | 11:00:00
Register #3 | 11:00:00 | 14:00:00 | 12:00:00
Register #3 | 11:00:00 | 14:00:00 | 13:00:00

最佳答案

试试这个:

SELECT *, start_time +
generate_series(0,extract(hour from (end_time - start_time))::int -1)
* interval '1 hour' as index_tag
FROM block

它有点难看,但它有效(generate_series 不适用于 time。仅 inttimestamp).

这是 SQLFiddle

关于 generate_series 的详细信息

关于带有索引标记的行的 postgresql 副本,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13614798/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com