56,687
社区成员
发帖
与我相关
我的任务
分享
--
-- 表的结构 `test`
--
CREATE TABLE IF NOT EXISTS `test` (
`pid` int(11) NOT NULL,
`tid` int(1) DEFAULT NULL,
`dateline` int(11) DEFAULT NULL,
PRIMARY KEY (`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `test`
--
INSERT INTO `test` (`pid`, `tid`, `dateline`) VALUES
(1368605, 111632, 1212230610),
(1368606, 111812, 1212230613),
(1368640, 111812, 1212230927),
(1368649, 111795, 1212231003),
(1368660, 111795, 1212231152),
(1368699, 111795, 1212231549),
(1368701, 111795, 1212231558),
(1368731, 111795, 1212231803),
(1368738, 111812, 1212231858);
/*
问题需求:
按照tid分组,然后每组中取出dateline值最大的记录
将上面得到的记录按照dateline排序后,取出top2对应的pid
结果:
pid
1368605
1368738
1368731
*/
drop table test
mysql> select * from test order by tid,dateline desc;
+---------+--------+------------+
| pid | tid | dateline |
+---------+--------+------------+
| 1368605 | 111632 | 1212230610 |
| 1368731 | 111795 | 1212231803 |
| 1368701 | 111795 | 1212231558 |
| 1368699 | 111795 | 1212231549 |
| 1368660 | 111795 | 1212231152 |
| 1368649 | 111795 | 1212231003 |
| 1368738 | 111812 | 1212231858 |
| 1368640 | 111812 | 1212230927 |
| 1368606 | 111812 | 1212230613 |
+---------+--------+------------+
9 rows in set (0.00 sec)
mysql> select pid from (select * from test order by tid,dateline desc) t group by tid;
+---------+
| pid |
+---------+
| 1368605 |
| 1368731 |
| 1368738 |
+---------+
3 rows in set (0.00 sec)
mysql>