c# - 计算查询中的总行数

Question

我有一个 SqlServer SELECT

DECLARE @offset INT;
DECLARE @limit INT;

WITH cte
     AS (SELECT t.*,
                Row_number() OVER (ORDER BY Id) AS RowNum
         FROM   (SELECT *
                 FROM   Table1
                 UNION
                 SELECT *
                 FROM   Table2) t)
SELECT *
FROM   cte
WHERE  RowNum BETWEEN @offset AND @offset + @limit

在没有 WHERE 条件的情况下，我如何知道限制的总行数 RowNum BETWEEN @offset AND @offset + @limit

Answer 1

使用逆行技巧

Row_number() OVER (ORDER BY Id asc) AS RowNum,
Row_number() OVER (ORDER BY Id desc) AS InverseRowNum,

然后 (RowNum + InverseRowNum) - 1 = 总行数