The disctinct group by query using mysql does not duplicate records
- 2020-05-07 20:33:22
- OfStack
There is a demand, 1 has not been solved, in google for a long time to find a solution, not one of the proposed scheme can be used, finally the devil done.
looks like this. Suppose 1 table:
id f_id value
1 2 a
2 2 b
3 5 c
4 9 c
5 9 a
6 6 d
id f_id value
1 2 a
2 2 b
3 5 c
4 9 c
5 9 a
6 6 d
id is the primary key, f_id is the foreign key, I need to get the data of the foreign key f_id which is not repeated, if I use group by or distinct, it is easy to solve
select f_id from table group by f_id
select distinct f_id from table
but if you try to get id in the result, it's going to be messy anyway. For example, if I want to sort the results by id, such as "select distinct f_id, id from table table order order desc" is completely in vain. After looking at a number of examples on google, I found that id needed to be manipulated in select to let mysql know what to do with id besides f_id. Such as Max, Min, Avg,Sun.. All is ok, then becomes the following code to solve...
select f_id, max(id) as id from table group by f_id order by id desc
is done, there is an article on the Internet very close to the answer, but it does not have "as id", resulting in the wrong results in my mysql execution, hehe.
looks like this. Suppose 1 table:
id f_id value
1 2 a
2 2 b
3 5 c
4 9 c
5 9 a
6 6 d
id f_id value
1 2 a
2 2 b
3 5 c
4 9 c
5 9 a
6 6 d
id is the primary key, f_id is the foreign key, I need to get the data of the foreign key f_id which is not repeated, if I use group by or distinct, it is easy to solve
select f_id from table group by f_id
select distinct f_id from table
but if you try to get id in the result, it's going to be messy anyway. For example, if I want to sort the results by id, such as "select distinct f_id, id from table table order order desc" is completely in vain. After looking at a number of examples on google, I found that id needed to be manipulated in select to let mysql know what to do with id besides f_id. Such as Max, Min, Avg,Sun.. All is ok, then becomes the following code to solve...
select f_id, max(id) as id from table group by f_id order by id desc
is done, there is an article on the Internet very close to the answer, but it does not have "as id", resulting in the wrong results in my mysql execution, hehe.