sql - 如何获取第二行

Question

我在下面有一个查询，用于制作视图。此查询提取report_id.

一个report_id 可以有多个唯一的report_info_id

SELECT DISTINCT ON (t.report_id) t.temporal_start_date,
    t.report_id,
    t.report_info_id,
    t.status
   FROM reports_infos t
  ORDER BY t.report_id, t.temporal_start_date DESC;

这导致数据类似于

temporal_start_date    |report_id|report_info_id|status                 |
-----------------------|---------|--------------|-----------------------|
    2006-09-22 00:00:00|       49|         20435|Validated              |
    2006-08-02 00:00:00|       89|         25782|Validated              |
    2006-11-13 00:00:00|      108|         20436|Validated              |
2020-12-09 09:27:29.214|      130|         31755|Edited after validation|

让我们以最后一条记录为例，其中 report_id= 130

 select report_info_id , report_id , temporal_start_date, temporal_end_date,status  from reports_infos where report_id=130 order by temporal_start_date desc

report_info_id|report_id|temporal_start_date    |temporal_end_date      |status                 |
--------------|---------|-----------------------|-----------------------|-----------------------|
         31755|      130|2020-12-09 09:27:29.214|                       |Edited after validation|
         29714|      130|2020-11-20 14:50:44.227|2020-12-09 09:27:29.215|Validated              |
         29713|      130|2020-11-20 14:49:27.088|2020-11-20 14:50:44.228|Edited after validation|
         25788|      130|    2006-03-20 00:00:00|2020-11-20 14:49:27.089|Validated              |

现在要求在第一个查询中，如果状态为“验证后已编辑”，我需要此类数据，然后拉出该 report_id 的第二行

预期结果

temporal_start_date    |report_id|report_info_id|status                 |
-----------------------|---------|--------------|-----------------------|
    2006-09-22 00:00:00|       49|         20435|Validated              |
    2006-08-02 00:00:00|       89|         25782|Validated              |
    2006-11-13 00:00:00|      108|         20436|Validated              |
2020-11-20 14:50:44.227|      130|         29714|Validated|

标签： sqlpostgresql

Answer 1

在这里使用ROW_NUMBER：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY report_id
                                 ORDER BY temporal_start_date DESC) rn
    FROM reports_info
)

SELECT temporal_start_date, report_id, report_info_id, status
FROM cte
WHERE rn = 2;