消除联合中的空值

Question

我正在跨具有相同结构的数据库进行查询，以显示从源值到目标值的映射。
我的每个数据库都有一个包含两列的表:source和

DB1

<表类> 源目标tbody><<tr>XYB空C空

这样就可以了，只要给出一个基于NULL的数字，然后选择第一个:

SELECT  TOP(1) WITH TIES UN.Source
,       UN.Target
FROM    (
Select t.Source, t.Target
from DB1.table t
union
Select t.Source, t.Target
from DB2.table t
) AS UN
ORDER BY DENSE_RANK()OVER(PARTITION BY UN.Source ORDER BY CASE WHEN UN.Target IS NOT NULL THEN 1 ELSE 2 END)

您可能会发现用最小值来考虑更容易:

with data as (
select Source, Target from DB1.<table> union
select Source, Target from DB2.<table>
), qualified as (
select *,
case when Target is not null or min(Target) over (partition by Source) is null
then 1 end as Keep
from data
)
select Source, Target from qualified where Keep = 1;

为了完整起见，以下是我根据@HoneyBadger的答案，包括@MartinSmith在评论中提出的建议而采用的解决方案

SELECT * FROM
(SELECT  UN.Source
,       UN.Target
,       DENSE_RANK()OVER(PARTITION BY UN.Source ORDER BY CASE WHEN UN.Target IS NOT NULL THEN 1 ELSE 2 END) as ranking
FROM    (
Select t.Source, t.Target
from DB1.table t
union
Select t.Source, t.Target
from DB2.table t
) AS UN
) UN2
WHERE UN2.ranking = 1
ORDER BY UN2.Source, UN2.Target

此方案只选择DENSE_RANK为1的记录，避免TOP(1) WITH TIES。