根据其中一列查找和更新非重复记录

Question

我想查找所有不重复的记录并更新其中一列。

前任。

Col_1 | Col_2 | Col_3 | Col_4 | Col_5
A     | AA    | BB    | 1     | 
A     | AB    | BC    | 2     |
A     | AC    | BD    | 3     |
B     | BB    | CC    | 1     |
B     | BB    | CC    | 2     |
C     | CC    | DD    | 1     |

我的查询必须按Col_1分组，我想根据 Col_2 和 Col3 找出不唯一的记录，然后更新Col_5。

基本上输出应该如下，

Col_1 | Col_2 | Col_3 | Col_4 | Col_5
A     | AA    | BB    | 1     | 1
A     | AB    | BC    | 2     | 1
A     | AC    | BD    | 3     | 1
B     | BB    | CC    | 1     | 0
B     | BB    | CC    | 2     | 0
C     | CC    | DD    | 1     | 0

有谁知道我该如何实现这一目标？这是一个大型数据库，因此性能也是一个关键因素。

谢谢堆，

Answer 1

有很多方法可以做到这一点。这个解决方案来自我可以访问的 postgres，但我敢打赌它也可以在 tsql 上运行，因为应该有通用语法。

;WITH
cte_1 AS (
SELECT col_1 FROM some_table GROUP BY col_1 HAVING count(*) > 1
),
cte_2 AS (
SELECT col_1 FROM some_table GROUP BY col_1, col_2, col_3 HAVING count(*) > 1
),
cte_3 AS (
SELECT cte_1.col_1 FROM cte_1
LEFT JOIN cte_2 ON cte_1.col_1 = cte_2.col_1
WHERE cte_2.col_1 IS NULL
)
UPDATE some_table SET col_5 = 1
FROM cte_3 WHERE cte_3.col_1 = some_table.col_1;

那么，上面会发生什么？

1. 首先，我们构建三个 CTE 半表，允许我们将逻辑拆分为更小的部分：

   • cte_1提取可以具有多个col2行和col_3行的行
   cte_2选择具有
   • 非唯一col_2和col_3
   cte_3，只需
   • LEFT JOIN返回那些具有唯一col_2和col_3col_1

2. 使用最后一个cte_3结构，我们能够正确更新some_table

我假设你的表在这里被称为some_table。如果您担心性能，您应该在此处提供一些主键，并且最好在col_2和col_3上建立索引(独立，但如果它们在(col_1, col_2)上复合，可能会有所帮助等等(。

此外，您可能希望将其从CTE中移动以使用临时表(也可以对其进行索引以提高效率。

另请注意，此查询适用于您的示例，但没有真实数据，它可能只是猜测。我的意思是，当您同时拥有col_1=A 一些独特且非 uniqecol_2时会发生什么？

但我认为这是个好起点。

Answer 2

;WITH
cte_1 AS (
SELECT col_1, count(*) as items FROM some_table GROUP BY col_1 HAVING count(*) > 1
),
cte_2 AS (
SELECT col_1, count(*) as items FROM some_table GROUP BY col_1, col_2, col_3 HAVING count(*) > 1
),
cte_3 AS (
SELECT cte_1.col_1 FROM cte_1
LEFT JOIN cte_2 ON cte_1.col_1 = cte_2.col_1
WHERE cte_2.col_1 IS NULL OR cte_1.items > cte_2.items
GROUP BY cte_1.col_1
)
UPDATE some_table SET col_5 = 1
FROM cte_3 WHERE cte_3.col_1 = some_table.col_1;