php - 获取与 MIN/MAX SUM 值关联的用户

Question

我有这个问题:

SELECT 
    MIN(totalDuration) AS lowestTotalDuration,
    MAX(totalDuration) AS highestTotalDuration
FROM 
    (SELECT 
        SUM(totalDuration) AS totalDuration
    FROM 
        activity
    WHERE 
        active = :active
    GROUP BY 
        userID
    ) activity

但是我还想获取与每个 MIN/MAX 结果相关联的用户 ID，以便我知道哪个用户拥有最少/最多。

我尝试将 userID 添加到查询中，但它只生成 MAX 值的 userID。

数据库结构是:

userID    totalDuration    active
---------------------------------
1         0.0100           1
1         0.3000           1
2         0.2000           1
2         0.1000           1
3         0.0020           1

查询应该产生:

([lowestTotalDuration] => 0.0020
 [lowestUserID] => 3
 [highestTotalDuration] => 0.3100
 [highestUserID] => 1)

谢谢。

Answer 1

如果查询运行时间不长，只需使用union all:

(SELECT userId, SUM(totalDuration) AS totalDuration
 FROM activity
 WHERE active = :active
 GROUP BY userID  
 ORDER BY totalDuration ASC
 LIMIT 1
) UNION ALL
(SELECT userId, SUM(totalDuration) AS totalDuration
 FROM activity
 WHERE active = :active
 GROUP BY userID  
 ORDER BY totalDuration DESC
 LIMIT 1
);

如果您有更大的数据，那么您可以为此目的使用变量:

SELECT userId, totalDuration
FROM (SELECT a.*,
             (@min := if(@min = -1 or @min < totalDuration, totalDuration, @min) as minval,
             (@max := if(@max = -1 or @max > totalDuration, totalDuration, @max) as maxval       
      FROM (SELECT userId, SUM(totalDuration) AS totalDuration
            FROM activity
            WHERE active = :active
            GROUP BY userID
           ) a CROSS JOIN
           (SELECT @min := -1, @max := -1) params
      ) a
WHERE totalDuration IN (minval, maxval);