SQL Server2012/在两个查询上添加row_number？

Question

我有一个关于在2个查询中添加行号的问题。

我的问题是：

SELECT 
    'telxm001001' AS Node, 
    '1' AS [Speed Dialing], 
    '0' AS [Spd DI Numbers by Range], 
    NULL as 'Speed Dialing No.',  
    REPLACE(TelefonGeschaeft, '+', '00') AS [Call Number], 
    Nachname AS [Directory Name], 
    Vorname AS [Directory First Name]
FROM
    dbo.MaData
WHERE        
    (TelefonGeschaeft LIKE '+%')

UNION ALL

SELECT       
    'telxm001001' AS Node, 
    '1' AS [Speed Dialing], 
    '0' AS [Spd DI Numbers by Range], 
    ROW_NUMBER() OVER (ORDER BY Nachname) AS 'Speed Dialing No.',    
    REPLACE(MobiltelefonGeschaeft, '+', '00') AS [Call Number], 
    Nachname AS [Directory Name], 
    Vorname AS [Directory First Name]
FROM
    dbo.MaData
WHERE        
    (MobiltelefonGeschaeft LIKE '+%')

我有一个包含1400个条目的SQL Server表。这两个查询得到了正确的结果，但行号不正确，因为当第二个查询开始时，计算从1开始。因此，在我的查询结果中，我有一个从1到680的数字，然后当第二个查询开始时，数字从1开始。有没有办法在两个查询完成后添加行编号，使计数从1到1400？

最好的问候时断时续

Answer 1

在派生查询上应用ROW_NUMBER排序；在各个查询中提供鉴别器(例如queryOrder)，以将相关结果分组。

SELECT ROW_NUMBER() OVER (ORDER BY queryOrder, [Directory Name]) AS number
FROM (
  SELECT 1 AS queryOrder, [Directory Name] ..
  UNION ALL
  SELECT 2 AS queryOrder, [Directory Name] ..
) j

Answer 2

在跳到这个查询之前，你想知道这两个查询的行号吗？

如果是，为什么在第一个查询中放入NULL？

如果您希望这两个查询都有行号，请在这两个查询上使用行号。

否则，尝试将它们放入另一个查询中，如下所示：

select 
     ROW_NUMBER over ..., Node, [Speed Dialing], .... 
from 
     (<actual query here>) as temp1;