DISTINCT 只有一列 [英] DISTINCT for only one column
本文介绍了DISTINCT 只有一列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有以下查询.
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屋!
查看全文