Row_Number() 在 Union all 查询中的继续应用

Question

如果我有三个表，如何获得递增的唯一编号？

例如：

• 第一个查询row_number结果= 1,2,3
• 第二个查询row_number结果= 4,5,6
• 第三个查询row_number结果= 7,8,9

我尝试了下面的查询，但只能得到前两个表的递增。

SELECT ROW_NUMBER() OVER(ORDER BY filename) AS SrNo,fileName FROM Tab1
UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY filename) + (SELECT COUNT(*) FROM tab1) AS 
SrNo, filename FROM Tab2
UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY filename) + (SELECT COUNT(*) FROM tab2) AS 
SrNo, filename FROM Tab3

每个表格有3条记录，我希望能够得到行号的结果为1、2、3、4、5、6、7、8、9。

- A.Hemrajani

Answer 1

使用子查询：

SELECT ROW_NUMBER() OVER (ORDER BY which, filename) as SrNo, filename
FROM (SELECT 1 as which, fileName FROM Tab1
      UNION ALL
      SELECT 2 as which, filename FROM Tab2
      UNION ALL
      SELECT 3 as which, filename FROM Tab3
     ) ttt;

- Gordon Linoff