SQL DISTINCT 只有一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5021693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DISTINCT for only one column
提问by Jonathan Wood
Let's say I have the following query.
假设我有以下查询。
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.
换句话说，当多行包含相同的电子邮件时，我希望结果只包含其中一行（最好是最后一行）。应允许在其他列中重复。
Clauses like DISTINCTand GROUP BYappear to work on entire rows. So I'm not sure how to approach this.
子句 likeDISTINCT和GROUP BY似乎适用于整行。所以我不确定如何处理这个问题。
回答by Chandu
If you are using SQL Server 2005 or above use this:
如果您使用的是 SQL Server 2005 或更高版本，请使用以下命令：
SELECT *
FROM (
SELECT ID,
Email,
ProductName,
ProductModel,
ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn
FROM Products
) a
WHERE rn = 1
EDIT: Example using a where clause:
编辑：使用 where 子句的示例：
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
回答by Pero P.
This assumes SQL Server 2005+ and your definition of "last" is the max PK for a given email
这假设 SQL Server 2005+ 并且您对“last”的定义是给定电子邮件的最大 PK
WITH CTE AS
(
SELECT ID,
Email,
ProductName,
ProductModel,
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID DESC) AS RowNumber
FROM Products
)
SELECT ID,
Email,
ProductName,
ProductModel
FROM CTE
WHERE RowNumber = 1
回答by jon3laze
When you use DISTINCTthink of it as a distinct row, not column. It will return only rows where the columns do not match exactly the same.
当您使用时DISTINCT，请将其视为不同的行，而不是列。它将仅返回列不完全相同的行。
SELECT DISTINCT ID, Email, ProductName, ProductModel
FROM Products
----------------------
1 | [email protected] | ProductName1 | ProductModel1
2 | [email protected] | ProductName1 | ProductModel1
The query would return both rows because the IDcolumn is different. I'm assuming that the IDcolumn is an IDENTITYcolumn that is incrementing, if you want to return the last then I recommend something like this:
查询将返回两行，因为ID列不同。我假设该ID列是一个IDENTITY递增的列，如果您想返回最后一个，那么我建议如下：
SELECT DISTINCT TOP 1 ID, Email, ProductName, ProductModel
FROM Products
ORDER BY ID DESC
The TOP 1will return only the first record, by ordering it by the IDdescending it will return the results with the last row first. This will give you the last record.
该TOP 1会只返回第一个记录，由责令ID下降，会先返回的结果与最后一排。这会给你最后的记录。
回答by Marshall Unduemi
You can over that by using GROUP BY function
你可以通过使用 GROUP BY 函数来解决这个问题
SELECT ID, Email, ProductName, ProductModel FROM Products GROUP BY Email回答by jRam90
For Access, you can use the SQL Select query I present here:
对于 Access，您可以使用我在此处提供的 SQL Select 查询：
For example you have this table:
例如你有这个表：
CLIENTE|| NOMBRES || MAIL
客户|| 名称 || 邮件
888 || T800 ARNOLD || [email protected]
第888章 T800 阿诺德 || [email protected]
123 || JOHN CONNOR || [email protected]
第123话 约翰康纳 || [email protected]
125 || SARAH CONNOR ||[email protected]
第125话 莎拉·康纳 ||[email protected]
And you need to select only distinct mails. You can do it with this:
并且您只需要选择不同的邮件。你可以这样做：
SQL SELECT:
SQL 选择：
SELECT MAX(p.CLIENTE) AS ID_CLIENTE
, (SELECT TOP 1 x.NOMBRES
FROM Rep_Pre_Ene_MUESTRA AS x
WHERE x.MAIL=p.MAIL
AND x.CLIENTE=(SELECT MAX(l.CLIENTE) FROM Rep_Pre_Ene_MUESTRA AS l WHERE x.MAIL=l.MAIL)) AS NOMBRE,
p.MAIL
FROM Rep_Pre_Ene_MUESTRA AS p
GROUP BY p.MAIL;
You can use this to select the maximum ID, the correspondent name to that maximum ID , you can add any other attribute that way. Then at the end you put the distinct column to filter and you only group it with that last distinct column.
您可以使用它来选择最大 ID，该最大 ID 的通讯员名称，您可以通过这种方式添加任何其他属性。然后最后将不同的列放入过滤器，然后只将它与最后一个不同的列组合在一起。
This will bring you the maximum ID with the correspondent data, you can use min or any other functions and you replicate that function to the sub-queries.
这将为您带来对应数据的最大 ID，您可以使用 min 或任何其他函数，并将该函数复制到子查询中。
This select will return:
此选择将返回：
CLIENTE|| NOMBRES || MAIL
客户|| 名称 || 邮件
888 || T800 ARNOLD || [email protected]
第888章 T800 阿诺德 || [email protected]
125 || SARAH CONNOR ||[email protected]
第125话 莎拉·康纳 ||[email protected]
Remember to index the columns you select and the distinct column must have not numeric data all in upper case or in lower case, or else it won't work. This will work with only one registered mail as well. Happy coding!!!
请记住对您选择的列进行索引，并且不同的列不能包含全部大写或小写的数字数据，否则将无法工作。这也仅适用于一封挂号邮件。快乐编码！！！
回答by JohnFx
The reason DISTINCTand GROUP BYwork on entire rows is that your query returns entire rows.
整行的原因DISTINCT和GROUP BY工作是您的查询返回整行。
To help you understand: Try to write out by hand what the query should return and you will see that it is ambiguous what to put in the non-duplicated columns.
帮助您理解：尝试手动写出查询应返回的内容，您会发现将哪些内容放入非重复列是不明确的。
If you literally don't care what is in the other columns, don't return them. Returning a random row for each e-mail address seems a little useless to me.
如果您真的不关心其他列中的内容，请不要返回它们。为每个电子邮件地址随机返回一行对我来说似乎有点无用。
回答by Abdullah Yousuf
Try This
尝试这个
;With Tab AS (SELECT DISTINCT Email FROM Products)
SELECT Email,ROW_NUMBER() OVER(ORDER BY Email ASC) AS Id FROM Tab
ORDER BY Email ASC
回答by Сергей Пустовит
Try this:
尝试这个：
SELECT ID, Email, ProductName, ProductModel FROM Products WHERE ID IN (SELECT MAX(ID) FROM Products GROUP BY Email)
