mysql - 根据 col2 的分组值捕获 col1 的值

Question

这个问题已经有答案了:

奥 git _a (27 个回答)

已关闭 4 年前。

这是我的表格的总体结构

USERID | QUANTITY | FRIEND_ID |         DATE
---------------------------------------------------
     1 |        3 |         7 | 2019-06-22 10:00:00
     2 |        2 |         8 | 2019-06-22 11:00:00
     1 |        2 |         5 | 2019-06-22 12:00:00

然后我想像这样对这些数据进行分组

SELECT USERID,
       SUM(QUANTITY) AS COUNT,
       // I NEED THE FRIEND_ID OF THE RECORD WITH MAX(DATE)
       MAX(DATE)
FROM THISTABLE
GROUP BY USERID

如何根据另一列的分组值选择一列的值？

Answer 1

您可以将查询连接到表中以获取 FRIEND_ID:

SELECT g.USERID, g.COUNT, t.FRIEND_ID, g.MAXDATE
FROM (
  SELECT USERID, SUM(QUANTITY) AS COUNT, MAX(DATE) AS MAXDATE
  FROM THISTABLE
  GROUP BY USERID
) g INNER JOIN THISTABLE t
on t.USERID = g.USERID AND t.DATE = g.MAXDATE