mysql - 基于 Max() 的多列 SELECT 和 GROUP BY

Question

当前表事件:

|  eventId  |  personId  |  type  | title   |  score  |
+-----------+------------+--------+---------+---------+
| 1         | 1          | movie  | Mission | 12      |
| 2         | 1          | movie  | UNCLE   | 32      |
| 3         | 1          | show   | U2      | 17      |
| 4         | 1          | show   | Leroy   | 13      |
| 5         | 2          | movie  | Holmes  | 19      |
| 6         | 2          | movie  | Compton | 14      |
| 7         | 2          | show   | Imagine | 22      |
| 8         | 2          | show   | Kane    | 22      |

MySQL 示例:

SELECT @personId:=personId as personId,
(
  SELECT title FROM Events 
  WHERE rate = (SELECT MAX(score) FROM Events) 
  AND type = ‘movie’ AND personId=@personId
) as movie,
(
  SELECT title FROM Events 
  WHERE rate = (SELECT MAX(score) FROM Events) 
  AND type = ‘movie’ AND personId=@personId
) as show,
FROM Events
GROUP BY personId
ORDER BY personId;

期望的输出:

|  personId  |  movie   |  show   |
+------------+----------+---------+
| 1          | UNCLE    | U2      |
| 2          | Holmes   | Imagine |

期望的结果是在事件表中显示每个 personId 的电影和节目的 MAX() 分数。我的实际输出包含 NULLS 并且需要很长时间才能加载。我的实际事件表有大约 20,000 个条目。

更新解决方案(源自前两个答案以提高性能)

SELECT e.personId,
(
  SELECT o.title 
  FROM Events o
  LEFT JOIN Events b 
      ON o.personId = b.personId AND o.score < b.score
      AND o.type = b.type
  WHERE o.type = 'movie' AND o.personId=e.personId  LIMIT 1
) as best_movie ,
(
  SELECT o.title
  FROM Events o
  LEFT JOIN Events b 
      ON o.personId = b.personId AND o.score < b.score
      AND o.type = b.type
  WHERE o.type = 'show' AND o.personId=e.personId  LIMIT 1
) as best_show
FROM Events e
GROUP BY e.personId
ORDER BY e.personId

Answer 1

我对您的查询做了一些修改。

看看:

SELECT @personId:=personId as personId,
(
  SELECT title FROM Events
  WHERE score = (SELECT MAX(score) FROM Events WHERE type = 'movie' 
  AND personId=@personId) 
  AND type = 'movie' AND personId=@personId  LIMIT 1
) as movie ,
(
  SELECT title FROM Events
  WHERE score = (SELECT MAX(score) FROM Events WHERE type = 'show' 
  AND personId=@personId) 
  AND type = 'show' AND personId=@personId  LIMIT 1
) as show1 
FROM Events
GROUP BY personId
ORDER BY personId