SQL用于识别在特定列中具有特定值的所有重复记录

Question

DB2中的数据如下所示:

<表类> GUID ID 键 注册表子项 tbody><<tr>abc - 123 - def1234567201abc - 123 - def1234567221abc - 123 - def1234567212abc - 123 - def1234568221abc - 124 - def1234667212abc - 124 - def1234668222abc - 125 - def1234767211abc - 125 - def1234768221

您可以将SUM(..) OVER (PARTITION BY .. )的窗口聚合组子句与

等条件一起使用。

SELECT GUID, ID, Key, SubKey
FROM ( SELECT t.*,
SUM(CASE WHEN SubKey = 1 THEN 1 END) OVER (PARTITION BY GUID) AS cnt
FROM t ) tt
WHERE cnt > 1 

选择要分组的列。

假设是GUID列。

SELECT GUID
FROM THE_TABLE
WHERE SUBKEY = 1
GROUP BY GUID
HAVING COUNT(SUBKEY) > 1