我在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 :

WITH cte AS (
  SELECT genre_name, actor_id, count(actor_id) AS cnt
    ,ROW_NUMBER() OVER(PARTITION BY genre_name ORDER BY count(actor_id) DESC) rn
  FROM movie
  JOIN movie_has_genre ON movie_has_genre.movie_id=movie.movie_id
  JOIN role            ON role.movie_id=movie.movie_id
  JOIN genre           ON genre.genre_id=movie_has_genre.genre_id  
  group by genre_name, actor_id 
)
SELECT *
FROM cte
WHERE rn <=3;

请不要使用过时的逗号连接语法。
工作原理：
根据genre\u name和actor\u id计算计数
每个流派名称获取3个最大值

赞(0）回复(0）举报 2021-06-23

ghhkc1vu2#

在mysql（8.0以前版本）中，可以使用变量：

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标准方法。
笔记：
不要在句子中使用逗号 FROM 条款。始终使用适当的、明确的 JOIN 语法。
使用表别名，该别名包含所用列的缩写。
限定您正在使用的查询中的所有列名，特别是 FROM 子句引用多个表。

convert-union-select-to-single-select-with-loop在mysql中的应用

2条答案

相关问题

热门标签

最新问答