如何仅根据A COLUMN的不同值选择行

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               a@a.com               Mr. A
2                 1               b@b.com               Mr. B
3                 1               c@c.com               Mr. C
4                 1               d@d.com               Mr. D
5                 1               a@a.com               Mr. A
6                 2               e@e.com               Mr. E
7                 2               a@a.com               Mr. A
8                 3               f@f.com               Mr. F
9                 4               d@d.com               Mr. D  
10                5               f@f.com               Mr. F
11                6               d@d.com               Mr. D

The result set should return:

结果集应返回：

Id                MailId          EmailAddress          Name
1                 1               a@a.com               Mr. A
2                 1               b@b.com               Mr. B
3                 1               c@c.com               Mr. C
4                 1               d@d.com               Mr. D
6                 2               e@e.com               Mr. E
8                 3               f@f.com               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的两行。

5 个解决方案

#1

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 id which is what your result seems to portray

这将为每个不同的电子邮件地址仅选择一行，具有最小ID的行是您的结果似乎描绘的内容

#2

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 version you're using...)

这适用于SQL Server 2005及更新版本（您没有提到您正在使用的版本......）

#3

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..

希望这有帮助..

#4

if you dont wanna use DISTINCT use GROUP BY

如果你不想使用DISTINCT使用GROUP BY

 SELECT * FROM myTABLE GROUP BY EmailAddress

#5

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 clause but you still won't have two rows against any particular MailId.

我看到我正在使用GROUP BY子句但你仍然没有针对任何特定MailId的两行。

#1