我已经得到了这个sql:
SELECT
artists.id,
actors.id,
actors.count
FROM
artists
INNER JOIN actors ON artists.title = actors.title;
演员表有重复和一些counter
字段。所以可以有
Tom Waits | 10
Tom Waits | 30
我如何改变我的第一个sql,所以它将只返回那些演员,谁有MAX(count)
SELECT
artists.id,
actors.id,
actors.count
FROM
artists
INNER JOIN actors ON artists.title = actors.title
HAVING MAX(actors.count);
SELECT actors.id
, MAX(actors.count)
FROM artists
INNER JOIN actors ON artists.title = actors.title
GROUP BY
actors.id
试试这个-
SELECT artists.id, actors.id, actors.`count` FROM artists
INNER JOIN actors
ON artists.title = actors.title
INNER JOIN (
SELECT title, MAX(`count`) max_count FROM actors
GROUP BY title
) actors2
ON actors.title = actors2.title AND actors.`count` = actors2.max_count;
SELECT artists.id, a.id, a.count
FROM artists
INNER JOIN ( SELECT actors.id, actors.count
FROM actors
HAVING MAX(actors.count) ) a ON artists.title = a.title
最简单的方法:
SELECT
artists.id,
(select actors.id
from actors
where artists.title = actors.title
order by actors.count desc Limit 1)
as actors_id,
(select actors.count
from actors
where artists.title = actors.title
order by actors.count desc Limit 1)
as actors.count
FROM
artists
;
SELECT
artists.id,
actors.id,
MAX(actors.count)
FROM
artists
INNER JOIN actors ON artists.title = actors.title;
GROUP BY
actors.id