问题是我们要获取给定行（例如由主键标识）之前和之后的总行数。
我尝试过在 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 等）中实现它
我认为这适用于任何方言：
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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
