显示row_number 2和next的结果

Question

我写了这个查询

SELECT 
ROW_NUMBER() OVER (ORDER BY b.s_id) as RN,
g.code as De, 
f.code as Ar
from a_c a
left join ne_a b ON b.n_a_id = a.n_a_id
left join Sh d ON d.s_id = b.s_id
left join A g ON g.a_id = a.d_a_id 
left join A f ON f.a_id = a.a_a_id 

where b.s_id = 'MHJIX'
ORDER BY b.s_id

它给我的结果是

RN  De  AR

1   S   D
2   D   G
3   G   J

我想要显示从Row_Number 2开始的结果。Row_numbers总是可以改变的。在上面的示例中，总共有3行，但在某些其他示例中，它可以是5行。如何显示row_number 2及更高版本的结果？

我该怎么做呢？

Answer 1

您需要类似这样的内容(CTE - Common Table Expression)：

;WITH YourCTE AS
(
   SELECT 
      ROW_NUMBER() OVER (ORDER BY b.s_id) as RN,
      g.code as De, 
      f.code as Ar,
      b.s_id
   FROM a_c a
   left join ne_a b ON b.n_a_id = a.n_a_id
   left join Sh d ON d.s_id = b.s_id
   left join A g ON g.a_id = a.d_a_id 
   left join A f ON f.a_id = a.a_a_id 
   where b.s_id = 'MHJIX'
)
SELECT * FROM YourCTE
WHERE RN >= 2
ORDER BY s_id

基本上，您需要将包含ROW_NUMBER()排名函数的查询“包装”到子查询中，这样您就可以在外部查询中引用新的"row_number“列(用它来限制最终结果集)。

Answer 2

您可以按照Marc的建议使用CTE，也可以使用派生表：

SELECT  b.RN,
        g.code as De, 
        f.code as Ar

FROM    a_c a
        LEFT JOIN (SELECT n_a_id, ROW_NUMBER() OVER (ORDER BY b.s_id) AS RN FROM ne_a) b ON b.n_a_id = a.n_a_id
        LEFT JOIN Sh d ON d.s_id = b.s_id
        LEFT JOIN A g ON g.a_id = a.d_a_id 
        LEFT JOIN A f ON f.a_id = a.a_a_id 

WHERE   b.s_id = 'MHJIX' AND b.RN >= 2