我在mysql 8.0中有一段很难看的代码。
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Adventure'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Music'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Fantasy'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Romance'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Family'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Comedy'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Short'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Animation'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Action'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Horror'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Sci-Fi'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Thriller'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Crime'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Drama'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Documentary'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Musical'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Mystery'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='War'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Western'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Adult'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
FROM movie_has_genre, role, movie, genre
where role.movie_id=movie.movie_id
and movie_has_genre.movie_id=movie.movie_id
and genre.genre_id=movie_has_genre.genre_id
and genre_name='Film-Noir'
group by genre_name, actor_id
ORDER BY count(actor_id) DESC
limit 3);
我想把它转换成更简洁的。基本上,唯一改变的是流派名称条件。此条件的21个值可以运行: select genre_name from genre;
我如何循环遍历genre\u name的21个值并将它们插入select查询,以增量方式构建union查询?谢谢。
2条答案
按热度按时间sqougxex1#
你可以用
ROW_NUMBER
:请不要使用过时的逗号连接语法。
工作原理:
根据genre\u name和actor\u id计算计数
每个流派名称获取3个最大值
ghhkc1vu2#
在mysql(8.0以前版本)中,可以使用变量:
在8.0+中,使用
row_number()
此功能的ansi标准方法。笔记:
不要在句子中使用逗号
FROM
条款。始终使用适当的、明确的JOIN
语法。使用表别名,该别名包含所用列的缩写。
限定您正在使用的查询中的所有列名,特别是
FROM
子句引用多个表。