SQL:给定行之前和之后的行数

Question

问题是我们要获取给定行之前和之后的总行数(例如，由主键标识)。

我曾尝试在 T-SQL (MSSQL 2008) 中遵循。它给出了正确的结果，但我不知道这是否是最好的方法。

;WITH cte_before AS 
(
    SELECT ROW_NUMBER() OVER ( Order By CustomerId ) [Row Number], customerid, 
           firstName 
    FROM SalesLT.Customer
 ),
cte_nums AS 
(
    SELECT ROW_NUMBER() OVER ( Order By CustomerId ) [Row Number1] 
    FROM SalesLT.Customer
)
SELECT [Row Number]-1 [before], MAX([Row Number1]) - [Row Number]
       , CustomerID, FirstName 
FROM cte_nums, cte_before
GROUP BY [Row Number], CustomerID, FirstName
HAVING CustomerID = 55

我们如何在 T-SQL 中改进它以及如何在其他 SQL 方言和服务器(如 Oracle、MySQL、sqlite、FireBird 等)中完成它？

Answer 1

我认为这适用于任何方言:

select 
   (select count(*) from SalesLT.Customer where customerid < 55) as rows_before,
   (select count(*) from SalesLT.Customer where customerid > 55) as rows_after,
   CustomerID, FirstName
from SalesLT.Customer
where CustomerID = 55