在 MySQL 中使用循环将 UNION 选择转换为单个选择 [英] Convert UNION selects to single select with loop in MySQL
问题描述
我在 MySQL 8.0 中有这段丑陋的代码.
I have this ugly code here in 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);
我想把它转换得更简洁.基本上,唯一改变的是genre_name 条件.此条件的 21 个值可以在运行时找到: 从流派中选择流派名称;
如何遍历流派名称的 21 个值并将它们插入到选择查询中,逐步构建 UNION 查询?谢谢.
and I want to convert it to be more concise. Basically, the only thing that changes is the genre_name condition. The 21 values for this condition can be found running: select genre_name from genre;
How can I loop through the 21 values of genre_name and plug them into the select query, incrementally building up the UNION query? Thanks.
推荐答案
在 MySQL(8.0 之前)中,您可以为此使用变量:
In MySQL (prior to 8.0), you can use variables for this:
select genre_name, actor_id, cnt
from (select genre_name, actor_id, cnt,
(@rn := if(@g = genre_name, @rn + 1,
if(@g := genre_name, 1, 1)
)
) as rn
from (select g.genre_name, r.actor_id, count(*) as cnt
from movie m join
role r
on r.movie_id = m.movie_id join
movie_has_genre mhg
on mhg.movie_id = m.movie_id join
genre g
on g.genre_id = mhg.genre_id
group by g.genre_name, r.actor_id
order by g.genre_name, cnt desc
) ga cross join
(select @g := '', @rn := 0) params
) ga
where rn <= 3;
在 8.0+ 中,使用 row_number()
此功能的 ANSI 标准方法.
In 8.0+, use row_number()
the ANSI standard method for this functionality.
注意事项:
- 从不在
FROM
子句中使用逗号.始终使用正确、明确的JOIN
语法. - 使用表别名,即您正在使用的列的缩写.
- 限定您正在使用的查询中的所有列名,尤其是当
FROM
子句引用多个表时.
- Never use commas in the
FROM
clause. Always use proper, explicitJOIN
syntax. - Use table aliases that the abbreviations for the columns you are using.
- Qualify all column names in the queries that you are using, especially when the
FROM
clause references more than one table.
这篇关于在 MySQL 中使用循环将 UNION 选择转换为单个选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!