如何使用一个不同的列选择多个列

Objective: using SqlServer 2005, Select multiple columns, but ensure that 1 specific column is not a duplicate

目标:使用SqlServer 2005,选择多个列,但确保1个特定列不重复

Issue: The following code does not remove the duplicates. The field that has duplicates is email.

问题:以下代码不会删除重复项。具有重复项的字段是电子邮件。

SELECT DISTINCT 
           email,
           name,
           phone
FROM
    database.dbo.table
WHERE
    status = 'active'
GROUP BY
    email,
    name,
    phone

Thank you in advance for any comments, suggestions or recommendations.

提前感谢您提出任何意见,建议或建议。

2 个解决方案

#1

It removes email duplicates but you have to decide which name, phone you need. The result is based on name, phone sort order.

它会删除电子邮件重复项,但您必须决定所需的名称和电话。结果基于姓名,电话排序顺序。

WITH cl
as
(
SELECT email, name, phone, ROW_NUMBER() OVER(PARTITION BY email ORDER BY name, phone) rn
FROM
    database.dbo.table
WHERE
    status = 'active')

select *
from cl
where rn =1

#2

This is a way of doing it

这是一种做法

DECLARE @Table AS TABLE
(email NVARCHAR(100), name NVARCHAR(100), phone NVARCHAR(100))

INSERT INTO @Table
        ( email , name , phone)
VALUES  ( N'fred', -- email  - nvarchar(100)
          N'bob', -- name - nvarchar(100)
          N'steve'  -- phone- nvarchar(100)
          )

          INSERT INTO @Table
        ( email , name , phone)
VALUES  ( N'fred', -- email - nvarchar(100)
          N'bob2', -- name - nvarchar(100)
          N'ste1ve'  -- phone- nvarchar(100)
          )

          INSERT INTO @Table
        ( email , name , phone)
VALUES  ( N'fred1', -- email - nvarchar(100)
          N'bob3', -- name - nvarchar(100)
          N'steve3'  -- phone- nvarchar(100)
          )


          SELECT email , MAX(name ) c2, MAX(col3) c3 FROM @Table GROUP BY email

#1