在SQL中，在一列中找到重复项，并为其他列指定唯一值(In SQL, find duplicates in one column with unique values for another colu

在SQL中，在一列中找到重复项，并为其他列指定唯一值(In SQL, find duplicates in one column with unique values for another column)

所以我有一个链接到记录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]

有什么解决方案可以更好地工作吗？ 就像在数据库上运行几十万条记录时不会导致服务器崩溃一样？

So I have a table of aliases linked to record ids. I need to find duplicate aliases with unique record ids. To explain better:

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

The result of a query on this table should be something to the effect of

000123 4 6 000345 6 7

Indicating that both record 4 and 6 have an alias of 000123 and both record 6 and 7 have an alias of 000345.

I was looking into using GROUP BY but if I group by alias then I can't select record id and if I group by both alias and record id it will only return the first two rows in this example where both columns are duplicates. The only solution I've found, and it's a terrible one that crashed my server, is to do two different selects for all the data and then join them

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

Are there any solutions out there that would work better? As in, not crash my server when run on a few hundred thousand records?

最满意答案

它看起来好像你有两个要求：

标识具有多个记录标识的所有别名，以及 水平列出这些别名的记录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在返回它们时做的事情水平。

It looks as if you have two requirements:

Identify all aliases that have more than one record id, and List the record ids for these aliases horizontally.

The first is a lot easier to do than the second. Here's some SQL that ought to get you where you want with the first:

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

Now, as for the second. If you're satisfied with the data in this form:

Alias Record ID 000123 4 000123 6 000345 6 000345 7

... you can stop there. Otherwise, things get tricky.

The PIVOT command will not necessarily help you, because it's trying to solve a different problem than the one you have.

I am assuming that you can't necessarily predict how many duplicate Record ID values you have per Alias, and thus don't know how many columns you'll need. If you have only two, then displaying each of them in a column becomes a relatively trivial exercise. If you have more, I'd urge you to consider whether the destination for these records (a report? A web page? Excel?) might be able to do a better job of displaying them horizontally than SQL Server can do in returning them arranged horizontally.