如何选择带有一个独立列的多个列 [英] How do you SELECT several columns with one distinct column

问题描述

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

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

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

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.

推荐答案

它删除重复的电子邮件，但您必须确定所需的姓名和电话.结果基于名称，电话排序顺序.

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  

这篇关于如何选择带有一个独立列的多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！