分组后获取每组的指定记录
【问题】
Table profile_match:
profile_id match_available_on
1 NULL
1 2015-05-12 03:50:09
1 2015-05-20 03:50:11
1 2015-05-31 03:50:17
2 2015-05-16 03:50:09
2 2015-05-11 03:52:30
2 2015-05-21 03:50:16
2 2015-05-28 03:50:17
Desired outcome: I want to get the 2nd earliest date for each group.
Current: I am getting the earliest date for each group
Query:
select * from (select * from profile_match where match_available_on is not null order by profile_id asc, match_available_on asc) as P1 group by P1.profile_id
Example:Link
From the example, I want to get:
· 1, ‘2015-05-20 03:50:11’
· 2, ‘2015-05-16 03:50:09’
And I hope that the query is not hard-coded with specific profile_id (1 or 2) as my actual data got lots of profile_id. Thanks guys!
【回答】
MySQL 没有窗口函数,写起来比较麻烦,用集算器会很方便:
A |
|
1 |
$select profile_id,match_available_on from profile_match where match_available_on!=null order by match_available_on |
2 |
=A1.group(profile_id).(~.m(2)) |