在 SQL 中，在另一列中查找具有唯一值的列中的重复项

Question

所以我有一个链接到记录 ID 的别名表。我需要找到具有唯一记录 ID 的重复别名。为了更好地解释：

 ID    Alias     Record ID
1     000123    4
2     000123    4
3     000234    4
4     000123    6
5     000345    6
6     000345    7

对该表的查询结果应该是

000123    4    6
000345    6    7

表示记录 4 和 6 的别名均为 000123，记录 6 和 7 的别名均为 000345。

我正在研究使用 GROUP BY 但如果我按别名分组，那么我无法选择记录 ID，如果我同时按别名和记录 ID 分组，它只会返回本示例中的前两行，其中两列都是重复的。我找到的唯一解决方案，这是一个让我的服务器崩溃的可怕解决方案，是对所有数据进行两次不同的选择，然后加入它们

ON [T_1].[ALIAS] = [T_2].[ALIAS] AND NOT [T_1].[RECORD_ID] = [T_2].[RECORD_ID]

有没有更好的解决方案？例如，在几十万条记录上运行时不会使我的服务器崩溃？

原文由 user1301877 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

看起来你有两个要求：

1. 识别具有多个记录 id 的所有别名，以及
2. 水平列出这些别名的记录 ID。

第一个比第二个容易得多。这里有一些 SQL 应该可以让你第一次到达你想要的地方：

 WITH A   -- Get a list of unique combinations of Alias and [Record ID]
AS  (
   SELECT Distinct
          Alias
     ,    [Record ID]
   FROM  T1
)
,   B  -- Get a list of all those Alias values that have more than one [Record ID] associated
AS  (
    SELECT Alias
    FROM   A
    GROUP BY
           Alias
    HAVING COUNT(*) > 1
)
SELECT  A.Alias
    ,   A.[Record ID]
FROM    A
    JOIN B
        ON  A.Alias = B.Alias

现在，至于第二个。如果您对此表格中的数据感到满意：

 Alias     Record ID
000123    4
000123    6
000345    6
000345    7

…你可以停在那里。否则，事情会变得棘手。

PIVOT 命令 不一定 会帮助您，因为它试图解决与您的问题不同的问题。

我假设您不一定能预测每个 Alias 有多少重复 Record ID 值，因此不知道您需要多少列。

如果您只有两个，那么将它们中的每一个显示在一个列中将成为一个相对微不足道的练习。如果你有更多，我会敦促你考虑这些记录的目的地（报告？网页？Excel？）在水平显示它们方面是否比 SQL Server 在返回它们方面做得更好水平。

原文由 Ann L. 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

我同意 Ann L 的回答，但想展示如何将窗口函数与 CTE 一起使用，因为您可能更喜欢可读性。

（回复：如何水平旋转，我再次同意安）

 create temporary table things (
  id serial primary key,
   alias varchar,
   record_id int
)

insert into things (alias, record_id) values
('000123', 4),
('000123', 4),
('000234', 4),
('000123', 6),
('000345', 6),
('000345', 7);

 with
    things_with_distinct_aliases_and_record_ids as (
        select distinct on (alias, record_id)
               id,
             alias,
             record_id
             from things
    ),
    things_with_unique_record_id_counts_per_alias as (
        select *,
             COUNT(*) OVER(PARTITION BY alias) as unique_record_ids_count
             from things_with_distinct_aliases_and_record_ids
  )

select * from things_with_unique_record_id_counts_per_alias
       where unique_record_ids_count > 1

第一个 CTE 获取所有唯一的别名/记录 ID 组合。例如

 id | alias  | record_id
----+--------+-----------
  1 | 000123 |         4
  4 | 000123 |         6
  3 | 000234 |         4
  5 | 000345 |         6
  6 | 000345 |         7

第二个 CTE 只是为上面创建一个新列，并为每个别名添加记录 id 的计数。这允许您仅过滤具有多个关联记录 id 的别名。

  id | alias  | record_id | unique_record_ids_count
----+--------+-----------+-------------------------
  1 | 000123 |         4 |                       2
  4 | 000123 |         6 |                       2
  3 | 000234 |         4 |                       1
  5 | 000345 |         6 |                       2
  6 | 000345 |         7 |                       2

原文由 robodisco 发布，翻译遵循 CC BY-SA 4.0 许可协议