替换选择的 SQL UNION 之后的空列

Question

我正在使用UNION来组合两个选择语句的结果。在返回的结果中，需要填写 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

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

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;