postgresql 获取一天中第二和第三个位置，时间不同

k0pti3hp 于 2022-12-29 发布在 PostgreSQL

关注(0)|答案(1)|浏览(239)

重点关注入职、午餐进出、公司离职
样本数据：

预期结果：

Select x."ID", MIN(x."FECHA") as "1pos", null "2pos", null "3pos", MAX(x."FECHA") as "4pos"
from sirha7.v_marcaciones x 
WHERE x."CEDULA" = '0401219282' AND CAST(x."FECHA" AS date) = '2022-12-27'
GROUP BY x."ID", DATE_TRUNC ('day', x."FECHA")
ORDER BY 2 DESC

postgresql

来源：https://stackoverflow.com/questions/74942905/obtain-the-second-and-third-position-of-a-day-with-different-hours

1条答案

按热度按时间

jdgnovmf1#

你可以使用array_agg()来收集with common table expression中所有按"ID"排序的"FECHA"，然后通过它们的索引来访问它们。如果你的一些"ID"没有4个"FECHA"，你可以使用array_upper()来获取最后一个元素的索引：

with cte as 
 (  select 
      x."ID", 
      array_agg(x."FECHA" order by x."FECHA") as all_fecha_per_id
    from sirha7.v_marcaciones x 
    WHERE x."CEDULA" = '0401219282' AND CAST(x."FECHA" AS date) = '2022-12-27'
    GROUP BY x."ID", DATE_TRUNC ('day', x."FECHA")   )
select 
  y."ID",
  all_fecha_per_id[array_lower(all_fecha_per_id,1)] as "1pos",
  all_fecha_per_id[1] as "1pos",
  all_fecha_per_id[2] as "2pos",
  all_fecha_per_id[3] as "3pos",
  all_fecha_per_id[4] as "4pos",
  all_fecha_per_id[array_upper(all_fecha_per_id,1)] as "4pos"
from cte as y
ORDER BY 2 DESC;

Online demo

赞(0）回复(0）举报 2022-12-29

我来回答

postgresql 获取一天中第二和第三个位置，时间不同

1条答案

相关问题

热门标签

最新问答