Row_Number() 与联合查询

问题描述：

我有一个 sql 查询，它从两个具有相同列名的表中返回行.我需要一个完整结果集中所有记录的行号.

I have an sql query that returns rows from two tables with same column names. I need a row number of all records in a complete result set.

以下是查询

select Id, VersionNumber from documents where id=5 
Union all  
select Id, VersionNumber from versions where id=5
order by VersionNumber desc

应该怎么做?

如果我只需要返回一个 row_num，其中 VersionNumber 是 5，该怎么办.

And what if I only need to return a row_num where VersionNumber is lets say 5.

答

试试这个:

SELECT *, ROW_NUMBER() OVER(ORDER BY Id) ROW_NUM
  FROM (
        select Id, VersionNumber from documents where id=5 
        Union all  
        select Id, VersionNumber from versions where id=5
       ) a
order by VersionNumber desc

按版本号 5 过滤使用:

TO filter by version number 5 use:

SELECT * 
FROM   (SELECT *, 
               Row_number() OVER(ORDER BY versionnumber DESC, id) row_num 
        FROM   (SELECT id, 
                       versionnumber 
                FROM   documents 
                WHERE  id = 5 
                UNION ALL 
                SELECT id, 
                       versionnumber 
                FROM   versions 
                WHERE  id = 5) a) b 
WHERE  version = 5

相关推荐