我使用此SQL查询(PostgreSQL):

select *
from (select  row_number()
      over (order by oid1, oid2, oid3)
      as row_number, *
      from snmpProperties)
as toto
where toto.row_number=(select row_number
                       from (select  row_number()
                             over (order by oid1, oid2, oid3)
                             as row_number, *
                             from snmpProperties)
                       as titi
                       where titi.oid1='4'
                             AND titi.oid2='1'
                             AND titi.oid3='')+1;

但它使用同一选择两次:
select  row_number()
    over (order by oid1, oid2, oid3)
    as row_number, *
    from snmpProperties

是否可以将我的请求分解为使用此select一次?
谢谢,
纪尧姆

最佳答案

select      *

from       (select      lag ((oid1,oid2,oid3)) over (order by oid1, oid2, oid3) = ('4','1','')  as is_requested_row
                       ,s.*

            from        snmpProperties as s
            ) as s

where       is_requested_row
;

附加解决方案,只是为了证明它也可以基于行数
select      *

from       (select      min (case when (oid1,oid2,oid3) = ('4','1','') then rn end) over ()   base_rn
                       ,s.*

            from       (select      row_number() over (order by oid1, oid2, oid3) as rn, s.*
                        from        snmpProperties as s
                        ) as s
            ) as s

where       rn = base_rn + 1
;

解释
(一)
使用LAG window函数,每个记录都会查看其上一个记录(按顺序为oid1、oid2、oid3)的(oid1、oid2、oid3)向量,并将其与('4','1','')进行比较。
当比较结果为真时,这意味着我们站在请求的记录上,该记录后面的记录具有oid1='4',oid2='1'和oid3=''。
2个)
使用“行数”窗口函数,我们为按oid1、oid2、oid3排序的记录指定行数。
使用min window函数,我们将在集合中的每一行中放置具有oid1、oid2和oid3值“4”、“1”和“”的行的行号。
我们取其行数等于行数+1的行

10-07 13:21