我正在开发一个 php 应用程序，需要从 mysql 表中检索最新记录。下面是表格设计和样本数据。
所以标准是：
按当前 DESC 排序，然后按 start_date DESC
预期产出
我的想法如下，有没有更好的解决方案？
SELECT *
FROM `experience` r
JOIN (
SELECT MAX(start_date) as date,
resume_id
FROM `experience`
WHERE present = 1
group by resume_id
)r2 ON r.resume_id = r2.resume_id
AND r.present = 1
AND r.start_date = r2.date
UNION
SELECT *
FROM `experience` r
JOIN (
SELECT MAX(start_date) as date,
resume_id
FROM `experience`
WHERE present = 0
group by resume_id
) r2 ON r.resume_id = r2.resume_id
AND r.present = 0
AND r.start_date = r2.date
WHERE NOT EXISTS (SELECT 1
FROM `experience`
WHERE present = 1
AND r.resume_id = resume_id
)
// took around 11-20 seconds to process above query on total 499,000 records
解释查询
更新：
http://sqlfiddle.com/#!9/bc5a12/1
CREATE TABLE `experience`(
`id` int(11) NOT NULL,
`resume_id` varchar(9) DEFAULT NULL,
`company_name` varchar(12) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
`present` varchar(7) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `experience`(
`id`,
`resume_id`,
`company_name`,
`start_date`,
`end_date`,
`present`)
VALUES
(1, '1', 'abc', '2009-01-01', '2009-12-31', '0'),
(2, '1', 'def', '2010-01-01', '2012-12-31', '0'),
(3, '1', 'ghi', '2013-01-01', '0000-00-00', '1'),
(4, '2', 'abc', '2009-01-01', '2009-12-31', '0'),
(5, '2', 'def', '2010-01-01', '2012-12-31', '0'),
(6, '2', 'ghi', '2013-01-01', '2016-01-01', '0'),
(7, '3', 'abc', '2009-01-01', '2009-12-31', '0'),
(8, '3', 'def', '2017-01-01', '0000-00-00', '1'),
(9, '3', 'ghi', '2010-01-01', '0000-00-00', '1');
ALTER TABLE `experience`
ADD PRIMARY KEY (`id`);
