时间戳

首页 > 解决方案 > Oracle SQL - 使用 LAG 或 LEAD 将多行结果返回到一行

sql - Oracle SQL - 使用 LAG 或 LEAD 将多行结果返回到一行

问题描述

我试图弄清楚如何为多行结果返回 1 行。

目前我的代码如下所示：

select x.Reference,
x.date "Date1",
x.char "Char1",
lead(x.date, 1) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Date2",
lead(x.char, 1) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Char2",
lead(x.date, 2) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Date3",
lead(x.char, 2) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Char3"

from tbl x

该表对每个 x.Reference 都有多个条目。对于每个 x.Reference 的第一行，该行返回所需的结果。但是，正如预期的那样，它会继续为找到的每个 x.reference 输出一行。我试图找到一种方法来限制这些额外行的输出，因为第一行已经有我需要的数据。

提前致谢。

标签： sqloraclelaglead

解决方案

好吧，您可以使用row_number()：

with x as (
      select x.Reference, x.date as "Date1", x.char as "Char1",
             lead(x.date, 1) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Date2",
             lead(x.char, 1) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Char2",
             lead(x.date, 2) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Date3",
             lead(x.char, 2) OVER (PARTITION BY x.Reference ORDER BY x.date) as "Char3",
             row_number() over (partition by x.Reference order by x.date) as seqnum
      from tbl x
     )
select x.*
from x
where seqnum = 1;

推荐阅读