mysql选择每组中第一个、第二个和最后一个值的最佳方法

ozxc1zmp 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(411)

我尝试使用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

在google了半天之后，我只能想出一个愚蠢的方法，分别执行以下两个查询，然后用我的服务器端代码合并结果：

//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

我敢肯定，一定有一种更优雅的方法可以一次性得到结果。有人能帮我达到我的要求吗？谢谢大家！

mysql

来源：https://stackoverflow.com/questions/50869192/mysql-best-way-to-select-first-second-and-last-value-in-each-group

2条答案

按热度按时间

a7qyws3x1#

好吧，你可以这样做：
选择第一个、第二个和最后一个语句，然后将它们连接在一起：

SELECT a.userID, a.purchaseTime 
    FROM fsl a WHERE a.purchaseTime = 
    (SELECT MAX(b.purchaseTime) FROM fsl b WHERE b.userID = a.userID)

SELECT a.userID, a.purchaseTime 
    FROM fsl a WHERE a.purchaseTime = 
    (SELECT MAX(c.purchaseTime) FROM fsl c 
        WHERE c.purchaseTime < (SELECT MAX(b.purchaseTime) 
            FROM fsl b WHERE b.userID = a.userID));

SELECT a.userID, a.purchaseTime 
    FROM fsl a WHERE a.purchaseTime = 
    (SELECT MIN(b.purchaseTime) 
        FROM fsl b WHERE b.userID = a.userID)

并使用连接将其拼接在一起：

SELECT fst.userID as userID, fst.purchaseTime as first, snd.purchaseTime as snd, trd.purchaseTime as last FROM
    (SELECT a.userID, a.purchaseTime 
    FROM fsl a 
    WHERE a.purchaseTime = 
        (SELECT MAX(b.purchaseTime) FROM fsl b WHERE b.userID = a.userID)) fst
    JOIN (SELECT a.userID, a.purchaseTime FROM fsl a 
            WHERE a.purchaseTime = (SELECT MAX(c.purchaseTime) 
            FROM fsl c WHERE c.purchaseTime < (SELECT MAX(b.purchaseTime) 
            FROM fsl b WHERE b.userID = a.userID))) snd
    ON fst.userID = snd.userID
    JOIN (SELECT a.userID, a.purchaseTime 
            FROM fsl a WHERE a.purchaseTime = 
            (SELECT MIN(b.purchaseTime) FROM fsl b WHERE b.userID = a.userID)) trd
    ON trd.userID = snd.userID;

然而，我不能保证这将是足够快的任何类型的生产使用。

赞(0）回复(0）举报 2021-06-20

ljsrvy3e2#

以下代码未经测试，但应该会给您一个好主意：

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

将它们合并到一个查询中得到上面的答案。

赞(0）回复(0）举报 2021-06-20

我来回答

mysql选择每组中第一个、第二个和最后一个值的最佳方法

2条答案

相关问题

热门标签

最新问答