从表中返回唯一的行

I have a table with 4 columns and 7 rows.

我有一个4列7行的表。

This table contains 1 customer with the same ID same LNAME and FNAME.

此表包含1个具有相同ID且LNAME和FNAME相同的客户。

Also the table has 2 customers with the same ID, but different LNAME or FNAME.

此外，该表还有2个客户具有相同的ID，但LNAME或FNAME不同。

That is the sales reps input error. Ideally my table should have only 2 rows (Row with ID_pk 3 and 7)

这是销售代表输入错误。理想情况下，我的表应该只有2行（ID_pk 3和7的行）

[![UNIQUE CUSTOMERS][3]][3]

[！[独特的客户] [3]] [3]

I need to have the following result-sets from the above table:

我需要从上表中得到以下结果集：

All unique rows by all the four columns (Row with ID_pk 3 and 7). (excluding case # 3 listed below)

所有四列的所有唯一行（ID_pk 3和7的行）。（不包括下面列出的案例＃3）
All duplicates by all the four columns (Row with ID_pk 3 and 8).

所有四列都是重复的（ID_pk 3和8的行）。
All duplicates by Customer_ID but with not matching LNAME and/or FNAME (Row with ID_pk 1, 2, 4 and 5) (these rows have to be sent back to sales reps for validation.)

所有重复的Customer_ID但不匹配LNAME和/或FNAME（行ID_pk 1,2,4和5）（这些行必须发送回销售代表进行验证。）

2 个解决方案

#1

Doing stuff this like relies heavily on nested queries, the GROUP BY clause, and the COUNT function.

这样做的东西很大程度上依赖于嵌套查询，GROUP BY子句和COUNT函数。

Part 1 - Unique rows

第1部分 - 独特的行

This query will show you all the rows where the customer ID has matching data.

此查询将显示客户ID具有匹配数据的所有行。

SELECT Customer_ID, Customer_FNAME, Customer_LNAME FROM dbo.customers WHERE Customer_ID IN (
    SELECT Customer_ID FROM (
        SELECT DISTINCT Customer_ID, Customer_FNAME, Customer_LNAME FROM dbo.customers
        GROUP BY Customer_ID, Customer_FNAME, Customer_LNAME
    ) Customers
    GROUP BY Customer_ID
    HAVING COUNT(Customer_ID) = 1
)
GROUP BY Customer_ID, Customer_FNAME, Customer_LNAME

Part 2 - Duplicates

第2部分 - 重复

This query will show you all the rows that have the same data entered more than once.

此查询将显示具有多次输入相同数据的所有行。

SELECT Customer_ID, Customer_FNAME, Customer_LNAME
FROM dbo.customers
GROUP BY Customer_ID, Customer_FNAME, Customer_LNAME
HAVING COUNT(Customer_ID) > 1

Part 3 - Mismatched Data

第3部分 - 不匹配的数据

This query is basically the same as the first, just looking for a different COUNT value.

此查询与第一个查询基本相同，只是查找不同的COUNT值。

SELECT Customer_ID, Customer_FNAME, Customer_LNAME FROM dbo.customers WHERE Customer_ID IN (
    SELECT Customer_ID FROM (
        SELECT DISTINCT Customer_ID, Customer_FNAME, Customer_LNAME FROM dbo.customers
        GROUP BY Customer_ID, Customer_FNAME, Customer_LNAME
    ) Customers
    GROUP BY Customer_ID
    HAVING COUNT(Customer_ID) > 1
)
GROUP BY Customer_ID, Customer_FNAME, Customer_LNAME

#2

You may use a CTE (Common Table expression): https://msdn.microsoft.com/en-us/library/ms175972.aspx

您可以使用CTE（公用表表达式）：https：//msdn.microsoft.com/en-us/library/ms175972.aspx

;WITH checkDup AS (
        SELECT Customer_ID, ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Customer ID) AS 'RN'
        FROM Table)
        SELECT Customer_ID FROM checkDup
        WHERE RN = 1;

Will give you your example output.

会给你你的例子输出。

You may manipulate the CTE to get the other results you seek.

您可以操纵CTE以获得您寻求的其他结果。

#1