Teradata SQL中UNION报错：集合运算符关联查询不允许Top N选项

报错原因 #

你遇到的错误是因为大多数SQL数据库引擎不允许在UNION这类集合运算符直接连接的查询中，同时使用TOP N和ORDER BY。子查询中的ORDER BY在集合运算场景下会被判定为无效，且语法上不支持这种直接嵌套写法。

修复方案 #

把每个带TOP 1的查询用子查询或CTE包裹，让数据库先单独计算出每个查询的TOP结果，再执行集合运算。

方案1：使用子查询包裹 #

SELECT city, count_ FROM (
    SELECT TOP 1
        city,
        COUNT(*) AS count_
    FROM unicorns
    WHERE city IS NOT NULL AND industry = 'Edtech'
    GROUP BY city
    ORDER BY 2 DESC
) AS edtech_top

UNION

SELECT city, count_ FROM (
    SELECT TOP 1
        city,
        COUNT(*) AS count_
    FROM unicorns
    WHERE city IS NOT NULL AND industry = 'Internet software & services'
    GROUP BY city
    ORDER BY 2 DESC
) AS internet_top

方案2：使用CTE（公共表表达式） #

WITH edtech_top AS (
    SELECT TOP 1
        city,
        COUNT(*) AS count_
    FROM unicorns
    WHERE city IS NOT NULL AND industry = 'Edtech'
    GROUP BY city
    ORDER BY 2 DESC
),
internet_top AS (
    SELECT TOP 1
        city,
        COUNT(*) AS count_
    FROM unicorns
    WHERE city IS NOT NULL AND industry = 'Internet software & services'
    GROUP BY city
    ORDER BY 2 DESC
)
SELECT city, count_ FROM edtech_top
UNION
SELECT city, count_ FROM internet_top

额外提示 #

• 若两个结果集可能出现重复数据（比如同一城市在两个行业均为TOP1），UNION会自动去重；如果需要保留所有结果（包括重复项），建议使用UNION ALL，执行效率更高。

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