sql-server - 在 select 的 SQL UNION 之后替换 NULL 列

Question

我正在使用 aUNION来组合两个 select 语句的结果。在返回的结果中，需要填写 NULL 值：

SELECT OrderNumber, OrderName 
FROM tblOrder 
WHERE OrderStatus ='New' 

UNION 

SELECT OrderNumber, OrderName 
FROM tblOrderHistory 
WHERE OrderStatus ='New' 
ORDER BY OrderNumber, OrderName

结果：

OrderNumber OrderName
---------------------
A001            abc
A001            abc
A001            NULL    
A001            NULL
A001            NULL
B002            xyz
B002            NULL
C003            abc

我的预期结果：

OrderNumber OrderName
---------------------
A001            abc
A001            abc
A001            abc
A001            abc
B002            xyz
B002            xyz
C003            abc

如何NULL通过匹配订单号来填充值，行中tblOrder的值始终是。OrderNametblOrderHistoryNULL

标签： sql-serverunion

Answer 1

对于快速解决方案，您可以考虑MAX()在此处用作分析函数：

WITH cte AS (
    SELECT OrderNumber, OrderName FROM tblOrder WHERE OrderStatus = 'New'
    UNION ALL
    SELECT OrderNumber, OrderName FROM tblOrderHistory WHERE OrderStatus = 'New'
)

SELECT
    OrderNumber,
    MAX(OrderName) OVER (PARTITION BY OrderNumber) OrderName
FROM cte
ORDER BY
    OrderNumber,
    OrderName;