mysql sql 字段唯一,SQL / mysql-选择唯一/唯一，但返回所有列？

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? Something like:

SELECT DISTINCT field1, * from table

解决方案

You're looking for a group by:

select *

from table

group by field1

Which can occasionally be written with a distinct on statement:

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

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

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

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.