mysql - 从电影中选择职业，其中年份 = 1950 AND 评分 =5 AND MAX(观看次数)；

Question

我需要帮助来解决这个问题。 “在 1950 年，这是观看次数最高的电影，根据该电影的用户职业，该电影的评分为 5，以及哪些职业的人经常观看该电影；”我收到以下错误“SemanticException [错误 10128]:第 1:56 行尚不支持 UDAF 'MAX'”

movie_name, year, rating, occupation, views
A   1950    1   teacher 52
B   1953    5   doctor  45
C   1950    1   teacher 85
D   1952    4   police  35
E   1951    2   lawyer  15
F   1950    3   doctor  26
G   1951    1   lawyer  53
H   1952    2   teacher 85
I   1951    4   police  45
J   1950    3   doctor  36
K   1953    1   lawyer  52
L   1951    5   police  62
M   1953    2   teacher 42
N   1950    4   lawyer  85
O   1951    3   police  9
P   1952    1   doctor  44
Q   1950    5   teacher 27
R   1951    2   police  86
S   1955    3   lawyer  51
T   1950    5   police  49
U   1955    4   teacher 86
V   1954    3   lawyer  99
W   1951    2   teacher 84
X   1955    5   police  72
Y   1954    2   teacher 62
Z   1950    4   doctor  85

Answer 1

不能在where子句中使用聚合表达式；对结果进行排序就足够了。

SELECT movie_name, occupation, views
FROM movie
WHERE year = 1950 AND rating = 5
ORDER BY views DESC
LIMIT 1