使用UNION合并同表最值查询结果报错，寻求技术帮助

错误原因 #

你的SQL报错核心是第一个子查询末尾的分号，分号会终止当前语句，导致后面的UNION被识别为独立的无效语句，数据库无法解析。

修复后的SQL（保留原逻辑） #

移除第一个查询末尾的分号，确保UNION与前后查询属于同一条完整语句：

select * from(
select  year,number_of_countries from (
    select year,max(countt) over(partition by year) as number_of_countries from (
        select year,noc,row_number() over(partition by year order by noc) as countt from (  
            select year,noc from athlete_events2
            group by year,noc
            order by year,noc ) t
            order by year)s)d
            group by number_of_countries,year
            order by number_of_countries)q
limit 1
union
select * from(
select  year,number_of_countries from (
    select year,max(countt) over(partition by year) as number_of_countries from (
        select year,noc,row_number() over(partition by year order by noc) as countt from (  
            select year,noc from athlete_events2
            group by year,noc
            order by year,noc ) t
            order by year)s)d
            group by number_of_countries,year
            order by number_of_countries desc
            )q
limit 1;

更简洁的优化写法 #

原SQL用row_number()统计国家数的逻辑过于冗余，直接用count(distinct noc)就能得到每年参赛国家数，再筛选最值年份更高效：

-- 获取参赛国家数最多的年份
SELECT year, number_of_countries
FROM (
    SELECT year, COUNT(DISTINCT noc) AS number_of_countries,
           RANK() OVER(ORDER BY COUNT(DISTINCT noc) DESC) AS rnk
    FROM athlete_events2
    GROUP BY year
) t
WHERE rnk = 1

UNION ALL

-- 获取参赛国家数最少的年份
SELECT year, number_of_countries
FROM (
    SELECT year, COUNT(DISTINCT noc) AS number_of_countries,
           RANK() OVER(ORDER BY COUNT(DISTINCT noc) ASC) AS rnk
    FROM athlete_events2
    GROUP BY year
) t
WHERE rnk = 1;

• 用COUNT(DISTINCT noc)直接统计每年参赛国家数，逻辑更清晰
• 用RANK()可处理多个年份并列最值的情况（比如多个年份国家数相同且为最值）
• 用UNION ALL替代UNION，避免不必要的去重操作，提升查询效率

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