Querying SQL table with different values in same c

问题:

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

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.

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.

Edit: I dont think I framed the question very well.

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.

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?

回答1:

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

回答2:

SELECT *
FROM myTable
WHERE ID IN (
      SELECT ID
      FROM myTable
      GROUP BY ID
      HAVING MAX(LastName) <> MIN(LastName) OR MAX(FirstName) <> MIN(FirstName) 
      )
ORDER BY ID, LASTNAME