根据单个不同的列选择唯一的行

I want to select rows that have a distinct email, see the example table below:

我要选择有不同电子邮件的行，请参见下面的示例表:

+----+---------+-------------------+-------------+
| id | title   | email             | commentname |
+----+---------+-------------------+-------------+
|  3 | test    | rob@hotmail.com   | rob         |
|  4 | i agree | rob@hotmail.com   | rob         |
|  5 | its ok  | rob@hotmail.com   | rob         |
|  6 | hey     | rob@hotmail.com   | rob         |
|  7 | nice!   | simon@hotmail.com | simon       |
|  8 | yeah    | john@hotmail.com  | john        |
+----+---------+-------------------+-------------+

The desired result would be:

期望的结果是:

+----+-------+-------------------+-------------+
| id | title | email             | commentname |
+----+-------+-------------------+-------------+
|  3 | test  | rob@hotmail.com   | rob         |
|  7 | nice! | simon@hotmail.com | simon       |
|  8 | yeah  | john@hotmail.com  | john        |
+----+-------+-------------------+-------------+

Where I don't care which id column value is returned. What would be the required SQL?

我不关心返回哪个id列值。所需的SQL是什么?

4 个解决方案

#1

Quick one in TSQL

快速在TSQL

SELECT a.*
FROM emails a
INNER JOIN 
  (SELECT email,
    MIN(id) as id
  FROM emails 
  GROUP BY email 
) AS b
  ON a.email = b.email 
  AND a.id = b.id;

#2

I'm assuming you mean that you don't care which row is used to obtain the title, id, and commentname values (you have "rob" for all of the rows, but I don't know if that is actually something that would be enforced or not in your data model). If so, then you can use windowing functions to return the first row for a given email address:

我假设您的意思是您不关心使用哪一行来获得标题、id和注释值(您对所有行都有“rob”，但我不知道这是否实际是在您的数据模型中强制执行的)。如果是，那么您可以使用窗口函数返回给定电子邮件地址的第一行:

select
    id,
    title,
    email,
    commentname

from
(
select 
    *, 
    row_number() over (partition by email order by id) as RowNbr 

from YourTable
) source

where RowNbr = 1

#3

Since you don't care which id to return I stick with MAX id for each email to simplify SQL query, give it a try

由于您不关心返回哪个id，所以我坚持每封邮件使用MAX id来简化SQL查询，请尝试一下

;WITH ue(id)
 AS
 (
   SELECT MAX(id)
   FROM table
   GROUP BY email
 )
 SELECT * FROM table t
 INNER JOIN ue ON ue.id = t.id

#4

-1

If you are using MySql 5.7 or later, according to these links (MySql Official, SO QA), we can select one record per group by with out the need of any aggregate functions.

如果您正在使用MySql 5.7或更高版本，根据这些链接(MySql官方，所以是QA)，我们可以根据任何聚合函数的需要，为每个组选择一条记录。

So the query can be simplified to this.

所以查询可以简化为这个。

select * from comments_table group by commentname;

用注释名称从comments_table组中选择*;

Try out the query in action here

在这里尝试执行查询

#1