查询具有相同 ID 的同一列中不同值的 SQL 表 [英] Querying SQL table with different values in same column with same ID

问题描述

我有一个带有 ID、First Name 和 Last name 的 SQL Server 2012 表.ID 每个人都是唯一的，但由于历史提要中的错误，不同的人被分配了相同的 ID.

I have an SQL Server 2012 table with ID, First Name and Last name. The ID is unique per person but due to an error in the historical feed, different people were assigned the same id.

------------------------------
    ID  FirstName   LastName
------------------------------    
    1    ABC         M
    1    ABC         M
    1    ABC         M
    1    ABC         N
    2    BCD         S
    3    CDE         T
    4    DEF         T
    4    DEG         T

在这种情况下，ID's 1 的人是不同的(他们的姓氏明显不同)但他们具有相同的ID.如何查询并得到结果?本例中的表有数百万行.如果它是一个较小的表，我可能会用 count > 查询所有 ID 的.1 并在excel中过滤它们.

In this case, the people with ID’s 1 are different (their last name is clearly different) but they have the same ID. How do I query and get the result? The table in this case has millions of rows. If it was a smaller table, I would probably have queried all ID’s with a count > 1 and filtered them in an excel.

我想要做的是，获取已分配给两个不同用户的所有此类 ID 的列表.

What I am trying to do is, get a list of all such ID's which have been assigned to two different users.

任何想法或帮助将不胜感激.

Any ideas or help would be very appreciated.

我不认为我很好地构建了这个问题.

I dont think I framed the question very well.

有两个 ID 多次出现.1 和 4.id 为 4 的行是相同的.我不希望在我的结果中出现这种情况.ID为1的行，虽然名字相同，但有1行姓氏不同.我只想要那些 ID 相同但名字或姓氏之一不同的 ID.

There are two ID's which are present multiple time. 1 and 4. The rows with id 4 are identical. I dont want this in my result. The rows with ID 1, although the first name is same, the last name is different for 1 row. I want only those ID's whose ID is same but one of the first or last names is different.

我尝试将多次出现的 ID 加载到临时表中，并尝试将其与父表进行比较，尽管没有成功.我可以尝试并实施其他任何想法吗?

I tried loading ID's which have multiple occurrences into a temp table and tried to compare it against the parent table albeit unsuccessfully. Any other ideas that I can try and implement?

推荐答案

SELECT
    ID
FROM
    <<Table>>
GROUP BY
    ID
HAVING
    COUNT(*) > 1;

这篇关于查询具有相同 ID 的同一列中不同值的 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！