SQL 如何仅根据列的不同值选择行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7454393/
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
how to select rows based on distinct values of A COLUMN only
提问by user576510
I need to query a table in order to return rows, but I am not able to query the table correctly. Here is my table view:
我需要查询表以返回行，但我无法正确查询表。这是我的表视图：
Id MailId EmailAddress Name
1 1 [email protected] Mr. A
2 1 [email protected] Mr. B
3 1 [email protected] Mr. C
4 1 [email protected] Mr. D
5 1 [email protected] Mr. A
6 2 [email protected] Mr. E
7 2 [email protected] Mr. A
8 3 [email protected] Mr. F
9 4 [email protected] Mr. D
10 5 [email protected] Mr. F
11 6 [email protected] Mr. D
The result set should return:
结果集应返回：
Id MailId EmailAddress Name
1 1 [email protected] Mr. A
2 1 [email protected] Mr. B
3 1 [email protected] Mr. C
4 1 [email protected] Mr. D
6 2 [email protected] Mr. E
8 3 [email protected] Mr. F
In other words: first, I want to select distinct e-mail addresses, and then return rows containing distinct e-mail addresses.
换句话说：首先，我想选择不同的电子邮件地址，然后返回包含不同电子邮件地址的行。
Note:Just using the "Distinct" keyword will not work here, as it will select distinct rows. My requirement is to select distinct email addresses, and then to select rows containing those addresses.
注意：仅使用“Distinct”关键字在这里不起作用，因为它会选择不同的行。我的要求是选择不同的电子邮件地址，然后选择包含这些地址的行。
Edit:I cannot use the "Group By" keyword either, because for this I will also have to Group By with Id (which is the PK) and doing this will return two rows with the same EmailAddress values but with different Ids.
编辑：我也不能使用“分组依据”关键字，因为为此我还必须使用 Id（这是 PK）分组，并且这样做将返回具有相同 EmailAddress 值但具有不同 Id 的两行。
回答by danishgoel
Looking at your output maybe the following query can work, give it a try:
查看您的输出也许以下查询可以工作，试一试：
SELECT * FROM tablename
WHERE id IN
(SELECT MIN(id) FROM tablename GROUP BY EmailAddress)
This will select only one row for each distinct email address, the row with the minimum idwhich is what your result seems to portray
这将为每个不同的电子邮件地址仅选择一行，最小的行id就是您的结果似乎所描绘的
回答by marc_s
Try this - you need a CTE (Common Table Expression) that partitions (groups) your data by distinct e-mail address, and sorts each group by ID - smallest first. Then you just select the first entry for each group - that should give you what you're looking for:
试试这个 - 您需要一个 CTE（公用表表达式），它按不同的电子邮件地址对您的数据进行分区（分组），并按 ID 对每个组进行排序 - 最小的在前。然后，您只需为每个组选择第一个条目 - 这应该为您提供所需的内容：
;WITH DistinctMails AS
(
SELECT ID, MailID, EMailAddress, NAME,
ROW_NUMBER() OVER(PARTITION BY EMailAddress ORDER BY ID) AS 'RowNum'
FROM dbo.YourMailTable
)
SELECT *
FROM DistinctMails
WHERE RowNum = 1
This works on SQL Server 2005 and newer(you didn't mention what versionyou're using...)
这适用于 SQL Server 2005 及更新版本（您没有提到您使用的版本......）
回答by Setmax
use this(assume that your table name is emails):
使用这个（假设你的表名是电子邮件）：
select * from emails as a
inner join
(select EmailAddress, min(Id) as id from emails
group by EmailAddress ) as b
on a.EmailAddress = b.EmailAddress
and a.Id = b.id
hope this help..
希望这有帮助..
回答by Satyarth Shankar
I am not sure about your DBMS. So, I created a temporary table in Redshift and from my experience, I think this query should return what you are looking for:
我不确定您的 DBMS。因此，我在 Redshift 中创建了一个临时表，根据我的经验，我认为此查询应该返回您要查找的内容：
select min(Id), distinct MailId, EmailAddress, Name
from yourTableName
group by MailId, EmailAddress, Name
I see that I am using a GROUP BY clausebut you still won't have two rows against any particular MailId.
我看到我正在使用 aGROUP BY clause但你仍然不会有针对任何特定MailId.
回答by blejzz
if you dont wanna use DISTINCT use GROUP BY
如果你不想使用 DISTINCT 使用 GROUP BY
SELECT * FROM myTABLE GROUP BY EmailAddress
