sql - SQL / mysql-选择唯一/唯一，但返回所有列？(SQL/mysql - Select distinct/UNIQUE but return all columns?)

Question

SELECT DISTINCT field1, field2, field3, ......   FROM table

I am trying to accomplish the following sql statement but I want it to return all columns is this possible?

(我正在尝试完成以下sql语句，但我希望它返回所有列，这可能吗？)

Something like:

(就像是：)

SELECT DISTINCT field1, * from table

  ask by aryaxt translate from so

Answer 1

You're looking for a group by:

(您正在寻找一个分组依据：)

select *
from table
group by field1

Which can occasionally be written with a distinct on statement:

(有时可以用不同的on语句编写：)

select distinct on field1 *
from table

On most platforms, however, neither of the above will work because the behavior on the other columns is unspecified.

(但是，在大多数平台上，上述两种方法都不起作用，因为未指定其他列上的行为。)

(The first works in MySQL, if that's what you're using.)

(（如果您使用的是第一个，则可在MySQL中使用。）)

You could fetch the distinct fields and stick to picking a single arbitrary row each time.

(您可以获取不同的字段，并坚持每次选择一个任意行。)

On some platforms (eg PostgreSQL, Oracle, T-SQL) this can be done directly using window functions:

(在某些平台（例如PostgreSQL，Oracle，T-SQL）上，可以直接使用窗口函数来完成此操作：)

select *
from (
   select *,
          row_number() over (partition by field1 order by field2) as row_number
   from table
   ) as rows
where row_number = 1

On others (MySQL, SQLite), you'll need to write subqueries that will make you join the entire table with itself ( example ), so not recommended.

(在其他（MySQL，SQLite）上，您需要编写子查询，这些查询将使您将整个表与自身连接（ 示例 ），因此不建议这样做。)