我有一个名为" person_data "的表:-
id req_type reqp_id reqt_id dev_id
1 track 11 12 33
2 stop_track 11 12 33
3 track 12 13 44
4 stop_track 12 13 44
5 track 12 13 45
6 track 13 14 55
现在的条件是i want:-
- 只有那些有"reqp_id, reqt_id, dev_id"列重复的行的最后一行。
- 所有行,如果它们没有这三个列作为重复。
我尝试的是:-
select max(id),min(req_type),reqp_id, reqt_id, dev_id
from person_data group by reqp_id, reqt_id, dev_id;
,输出和我想要的完全一样:-
id req_type reqp_id reqt_id dev_id
2 stop_track 11 12 33
4 stop_track 12 13 44
5 track 12 13 45
6 track 13 14 55
,如果我使用:-
select max(id),max(req_type),reqp_id, reqt_id, dev_id
from person_data group by reqp_id, reqt_id, dev_id;
则只有"req_type"列的输出更改为:-
req_type
track
track
track
track
但是输出清楚地描述了使用字母顺序排序值的最大或最小函数,否则如果有人改变"req_type"列的值,那么输出将被改变。提前感谢,
试试这个
SELECT T.* FROM person_data T JOIN
(
SELECT Max(id) as maxid,reqp_id, reqt_id, dev_id
FROM person_data
GROUP BY reqp_id, reqt_id, dev_id
) S
ON T.id = S.maxid
<<p> 小提琴演示/strong> 输出将是:
id req_type reqp_id reqt_id dev_id
2 stop_track 11 12 33
4 stop_track 12 13 44
5 track 12 13 45
6 track 13 14 55