我正在尝试使用MySQL获取表中每个组的第一个，第二个和最后一个值。我的数据行是这样的：
userID purchaseTime
----------------------
1 2018-01-01
1 2018-01-02
1 2018-01-03
1 2018-01-04
2 2018-02-01
2 2018-02-02
3 2018-03-01
预期结果将是：
userID first second last
------------------------------------------------
1 2018-01-01 2018-01-02 2018-01-04
2 2018-02-01 2018-02-02 2018-02-02
3 2018-03-01 null 2018-03-01
搜寻了半天后，我只能找出一种愚蠢的方式来分别执行以下两个查询，然后通过服务器端代码合并结果：
//get 1st, 2nd values
SELECT userID, purchaseTime
FROM purchaseLog t1
WHERE
(
SELECT COUNT(*)
FROM purchaseLog t2
WHERE t2.userID = t1.userID AND
t2.purchaseTime<= t1.purchaseTime
) <= 2 order by t1.userID , t1.purchaseTime;
//get last value
SELECT max(purchaseTime) FROM purchaseTime GROUP BY userID
我非常确定，必须有一种更优雅的方法来一次性获得结果。谁能帮助我达到要求？谢谢你们！
以下代码未经测试，但可以为您提供一个好主意：
SELECT
t1.userID,
t1.purchaseTime AS first,
t2.purchaseTime AS `second`,
t4.purchaseTime AS last
FROM purchaseLog t1
LEFT JOIN purchaseLog t0 ON t1.userID = t0.userID AND t0.purchaseTime < t1.purchaseTime
LEFT JOIN purchaseLog t2 ON t1.userID = t2.userID AND t1.purchaseTime < t2.purchaseTime
LEFT JOIN purchaseLog t3 ON t1.userID = t3.userID AND t1.purchaseTime < t3.purchaseTime
AND t3.purchaseTime < t2.purchaseTime
JOIN purchaseLog t4 ON t1.userID = t4.userID AND t1.purchaseTime <= t4.purchaseTime
LEFT JOIN purchaseLog t5 ON t1.userID = t5.userID AND t4.purchaseTime < t5.purchaseTime
WHERE t0.purchaseTime IS NULL AND t3.purchaseTime IS NULL AND t5.purchaseTime IS NULL
让我一步步分解一下：
首先，获取所有不存在相同userID的较早行的行：
SELECT
t1.userID,
t1.purchaseTime AS first
FROM purchaseLog t1
LEFT JOIN purchaseLog t0 ON t1.userID = t0.userID AND t0.purchaseTime < t1.purchaseTime
WHERE t0.purchaseTime IS NULL
接下来，我得到所有purchaseTime大于第一个purchaseTime的行，在这两个行之间没有没有purchaseTime的行：
SELECT
t1.userID,
t2.purchaseTime AS `second`
FROM purchaseLog t1
LEFT JOIN purchaseLog t2 ON t1.userID = t2.userID AND t1.purchaseTime < t2.purchaseTime
LEFT JOIN purchaseLog t3 ON t1.userID = t3.userID AND t1.purchaseTime < t3.purchaseTime
AND t3.purchaseTime < t2.purchaseTime
WHERE t3.purchaseTime IS NULL
最后，我获得了purchaseTime大于或等于第一个不大于purchaseTime的行：
SELECT
t1.userID,
t4.purchaseTime AS last
FROM purchaseLog t1
JOIN purchaseLog t4 ON t1.userID = t4.userID AND t1.purchaseTime <= t4.purchaseTime
LEFT JOIN purchaseLog t5 ON t1.userID = t5.userID AND t4.purchaseTime < t5.purchaseTime
WHERE t5.purchaseTime IS NULL
将它们全部组合成一个查询即可得到以上答案。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句