TSQL 联合获得唯一价值

Question

我正在尝试根据两个条件获取两个数据库之间的唯一记录。 标准是：

1. 如果在数据库 1 中找到数据（在下面的示例中@SCCM），则被优先考虑
2. 获取所选数据库中的 MAX 资源 ID

这是一个例子，它工作了一半。 数据库首选项正在工作，但该数据库中的最大资源 ID 不起作用。 现在，它正在@SMS和@SCCM之间选择最大值

DECLARE @SMS TABLE (
    name0 varchar(100),
    resid int
)
DECLARE @SCCM TABLE (
    name0 varchar(100),
    resid int
)
INSERT INTO @SMS
SELECT 'TEST', 1000 UNION
SELECT 'TEST', 1500 UNION
SELECT 'TEST1', 2000 UNION
SELECT 'TEST2', 3000 UNION
SELECT 'TEST3', 4000
INSERT INTO @SCCM
SELECT 'TEST', 100 UNION
SELECT 'TEST', 150 UNION
SELECT 'TEST1', 200 UNION
SELECT 'TEST2', 300
SELECT MIN(SMSDB) as SMSDB, MAX(Resid), Name0 FROM
(
    SELECT name0, resid, 2 as SMSDB FROM @SMS
    UNION ALL
    SELECT name0, resid, 1 as SMSDB FROM @SCCM
) as tbl
GROUP BY NAME0

预期成果：

SMSDB | Resid | Name0
----------------------
1     | 150   | TEST
1     | 200   | TEST1
1     | 300   | TEST2
2     | 4000  | TEST3

Answer 1

您可以使用分区：

;WITH tbl as
(
    SELECT name0, resid, 2 as SMSDB FROM SMS
    UNION ALL
    SELECT name0, resid, 1 as SMSDB FROM SCCM
),
t as (
    SELECT *, 
           ROW_NUMBER()  
           over (partition By name0 order by SMSDB, resid desc ) 
           as rn
    FROM tbl
)
SELECT * FROM t
WHERE rn = 1

结果：

| NAME0 | RESID | SMSDB | RN |
------------------------------
|  TEST |   150 |     1 |  1 |
| TEST1 |   200 |     1 |  1 |
| TEST2 |   300 |     1 |  1 |
| TEST3 |  4000 |     2 |  1 

Answer 2

分区解决方案实际上可能更好，但它伤害了我的大脑。 如果 SMS 中的值存在于 SCCM 中，则仅排除这些值怎么样？

SELECT MIN(SMSDB) as SMSDB, MAX(Resid), Name0 FROM
(
    SELECT name0, resid, 2 as SMSDB FROM @SMS SMS
    WHERE NOT EXISTS (SELECT * FROM @SCCM WHERE name0 = SMS.name0)
    UNION ALL
    SELECT name0, resid, 1 as SMSDB FROM @SCCM
) as tbl
GROUP BY NAME0

甚至

SELECT 1 as SMSDB, MAX(resid), name0  FROM @SCCM
GROUP BY name0
UNION ALL
SELECT 2 as SMSDB, MAX(resid), name0 FROM @SMS SMS
WHERE NOT EXISTS (SELECT * FROM @SCCM WHERE name0 = SMS.name0)
GROUP BY name0
ORDER BY name0