MySQL queries for all records that a field does not duplicate
- 2020-05-07 20:34:44
- OfStack
Suppose you now have the following N record called book
id author title
1 aaa AAA
2 bbb BBB
3 ccc CCC
4 ddd DDD
5 eee AAA
Now you want to query all of the five records that title does not repeat
select distinct title,author from book this is not possible because distinct can only work on one field
Want to ask how should write
The answer:
If you choose the record that matches the first, then min(id) will do
id author title
1 aaa AAA
2 bbb BBB
3 ccc CCC
4 ddd DDD
5 eee AAA
Now you want to query all of the five records that title does not repeat
select distinct title,author from book this is not possible because distinct can only work on one field
Want to ask how should write
The answer:
select a.* from book a right join (
select max(id) id from book group by title) b on b.id = a.id
where a.id is not null
If you choose the record that matches the first, then min(id) will do
select a.* from book a right join (
select min(id) id from book group by title) b on b.id = a.id
where a.id is not null