MySQL禁用UNION单查询获取表首尾行 求更优方案

If you want to skip using UNION while retrieving the shortest city (with the alphabetically smallest name in case of ties) and longest city (same tiebreaker) from your country table, window functions are a clean, efficient way to do it in one pass over your data. Here's the implementation:

WITH ranked_cities AS (
    SELECT 
        city,
        LENGTH(city) AS city_length,
        -- Assign rank to find the shortest city (sorted by length ascending, then city name ascending)
        ROW_NUMBER() OVER (ORDER BY LENGTH(city) ASC, city ASC) AS shortest_rank,
        -- Assign rank to find the longest city (sorted by length descending, then city name ascending)
        ROW_NUMBER() OVER (ORDER BY LENGTH(city) DESC, city ASC) AS longest_rank
    FROM country
)
SELECT city, city_length
FROM ranked_cities
WHERE shortest_rank = 1 OR longest_rank = 1;

How This Works: #

• CTE ranked_cities: We calculate two rank values for every row in the table:
  • shortest_rank: Orders rows first by city length (smallest to largest), then by city name (A-Z). The row with shortest_rank = 1 is your target shortest city.
  • longest_rank: Orders rows first by city length (largest to smallest), then by city name (A-Z). The row with longest_rank = 1 is your target longest city.
• Final Filter: We pick only the rows where either rank equals 1, giving you exactly the two rows you need (or one row if all cities share the same length).

Why This Is Better Than the UNION Approach: #

• This query scans the country table only once (the original UNION approach scans twice), which is significantly faster for large datasets.
• Window functions are optimized in all modern databases (PostgreSQL, MySQL 8+, SQL Server, etc.), so this will perform well even with millions of rows.

Edge Case Note: #

• If every city has the same length, this query returns a single row instead of duplicating it (unlike the original UNION ALL). If you specifically need duplicate rows in this scenario, you’d have to add extra logic, but that’s a rare edge case most users don’t need to handle.

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