如何将MySQL 8.0中多UNION的SELECT改写为带循环的单查询？

Hey there! That repetitive UNION chain is definitely a pain to maintain—let's fix that with MySQL 8.0's window functions, which are perfect for per-group ranking tasks like this. No loops required (they'd actually be less efficient here anyway!).

The Clean, Single-Query Solution #

Instead of writing the same SELECT block 16 times, we can compute all the counts in one pass, rank actors within each genre, then filter for the top 3 per genre:

WITH genre_actor_counts AS (
    SELECT 
        g.genre_name,
        r.actor_id,
        COUNT(r.actor_id) AS actor_count,
        ROW_NUMBER() OVER (
            PARTITION BY g.genre_name 
            ORDER BY COUNT(r.actor_id) DESC
        ) AS rank_in_genre
    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
    WHERE 
        g.genre_name IN (
            'Adventure', 'Music', 'Fantasy', 'Romance', 'Family',
            'Comedy', 'Short', 'Animation', 'Action', 'Horror',
            'Sci-Fi', 'Thriller', 'Crime', 'Drama', 'Documentary', 'Musical'
        )
    GROUP BY 
        g.genre_name, r.actor_id
)
SELECT genre_name, actor_id, actor_count
FROM genre_actor_counts
WHERE rank_in_genre <= 3
ORDER BY genre_name, rank_in_genre;

What This Does: #

1. CTE (genre_actor_counts): First, we join all your tables properly (using explicit JOIN syntax instead of comma-separated tables for better readability) and calculate how many times each actor appears in each genre.
2. Window Function (ROW_NUMBER()): This assigns a rank to each actor within their genre, ordered by their count descending. The PARTITION BY g.genre_name ensures we rank actors separately per genre.
3. Filter Top 3: Finally, we select only the rows where the rank is 3 or lower, giving us the top 3 actors for each specified genre.

Why This Is Better Than Your Original Code: #

• Maintainable: Add/remove genres by just updating the IN list instead of copying entire SELECT blocks.
• Efficient: Scans your tables once instead of 16 times (one per UNION block), which will be faster on large datasets.
• Cleaner: No redundant code to debug or update.

If you want to include all genres (not just the 16 listed), you can simply remove the entire WHERE g.genre_name IN (...) clause.

内容的提问来源于stack exchange，提问作者SteliosM