左边加入正确的表有Count = 1的地方 [英] Left Join Where the right table has Having Count = 1

问题描述

表1：

ID     Values
1      1
2      2
3      3
4      4

表2：

ID    Values
1     1
1     2
1     2
2     4
4     5
4     6

我需要一个结果：

ID    Table 1 Values    Table 2 Values
1     1                 1
2     2                 4
3     3                         *This is blank because Table 2 doesn't have ID 3
4     4                 5
4     4                 6

请注意排除行表2重复值（ID 1和值2）。然而，ID为4的2行，因为来自表2的ID为4的值都不具有重复性。

Notice the Exclusion of the rows in table 2 Where the Values are duplicated ( ID 1 and Value 2). Yet the 2 rows for ID 4 because both values from Table 2 for ID 4 do not have duplicity.

所以这是一个左连接，我只想从表中的值2，该值不重复，ID与表1中的ID相匹配。

So this is a left join and I want only the Value from table 2 where that Value is not duplicated AND the ID matches an ID from Table 1.

推荐答案

在表2中使用CTE，然后使用它作为您左边连接的右侧：

Use a CTE over table 2, and then use that as the right side of your left join:

WITH t2 AS (
    SELECT ID, Values FROM [Table 2]
    GROUP BY ID, Values
    HAVING COUNT(*) = 1
)
SELECT t1.ID, t1.Values [Table 1 Values], t2.Values [Table 2 Values]
    FROM [Table 1] t1
    LEFT JOIN t2 ON t1.ID = t2.ID

这篇关于左边加入正确的表有Count = 1的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！