DISTINCT 只有一列 [英] DISTINCT for only one column

问题描述

假设我有以下查询.

SELECT ID, Email, ProductName, ProductModel FROM Products

如何修改它以使其不返回重复的电子邮件?

How can I modify it so that it returns no duplicate Emails?

换句话说，当多行包含相同的电子邮件时，我希望结果只包含其中一行(最好是最后一行).应允许在其他列中重复.

In other words, when several rows contain the same email, I want the results to include only one of those rows (preferably the last one). Duplicates in other columns should be allowed.

像 DISTINCT 和 GROUP BY 这样的子句似乎适用于整行.所以我不知道如何解决这个问题.

Clauses like DISTINCT and GROUP BY appear to work on entire rows. So I'm not sure how to approach this.

推荐答案

如果您使用的是 SQL Server 2005 或更高版本，请使用:

If you are using SQL Server 2005 or above use this:

SELECT *
  FROM (
                SELECT  ID, 
                        Email, 
                        ProductName, 
                        ProductModel,
                        ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn
                    FROM Products
              ) a
WHERE rn = 1

使用 where 子句的示例:

Example using a where clause:

SELECT *
  FROM (
                SELECT  ID, 
                        Email, 
                        ProductName, 
                        ProductModel,
                        ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn
                    FROM Products
                   WHERE ProductModel = 2
                     AND ProductName LIKE 'CYBER%'

              ) a
WHERE rn = 1

这篇关于DISTINCT 只有一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！