如何为SQL年度盗垒领袖查询设置别名？及避免深层嵌套方案

首先，先解决你提到的别名无效问题——大概率是子查询的写法不对。用CTE（公共表表达式）定义sb_leaders是最清晰的方式，先把每个联盟每年的盗垒领袖（含并列情况）筛选出来，再通过自连接把NL和AL的行合并成一行：

WITH sb_leaders AS (
    SELECT 
        yearID,
        lgID,
        playerID,
        SB,
        -- 用RANK()处理并列，若只需唯一第一可换ROW_NUMBER()
        RANK() OVER (PARTITION BY yearID, lgID ORDER BY SB DESC) AS sb_rank
    FROM batting
)
SELECT 
    nl.yearID,
    nl.playerID AS nl_leader_id,
    nl.SB AS nl_sb,
    al.playerID AS al_leader_id,
    al.SB AS al_sb
FROM sb_leaders nl
INNER JOIN sb_leaders al 
    ON nl.yearID = al.yearID
    AND nl.lgID = 'NL'
    AND al.lgID = 'AL'
    AND nl.sb_rank = 1
    AND al.sb_rank = 1;

如果你的数据库不支持CTE，也可以用嵌套子查询加别名，注意子查询必须用括号包裹：

SELECT 
    nl.yearID,
    nl.playerID AS nl_leader_id,
    nl.SB AS nl_sb,
    al.playerID AS al_leader_id,
    al.SB AS al_sb
FROM (
    SELECT 
        yearID,
        lgID,
        playerID,
        SB,
        RANK() OVER (PARTITION BY yearID, lgID ORDER BY SB DESC) AS sb_rank
    FROM batting
) nl
INNER JOIN (
    SELECT 
        yearID,
        lgID,
        playerID,
        SB,
        RANK() OVER (PARTITION BY yearID, lgID ORDER BY SB DESC) AS sb_rank
    FROM batting
) al 
    ON nl.yearID = al.yearID
    AND nl.lgID = 'NL'
    AND al.lgID = 'AL'
    AND nl.sb_rank = 1
    AND al.sb_rank = 1;

避免深层嵌套的替代方法 #

1. 条件聚合
   直接用CASE语句把两行数据转成一行，无需额外关联查询，结构更紧凑：

SELECT 
    yearID,
    -- 提取NL联盟领袖信息
    MAX(CASE WHEN lgID = 'NL' AND sb_rank = 1 THEN playerID END) AS nl_leader_id,
    MAX(CASE WHEN lgID = 'NL' AND sb_rank = 1 THEN SB END) AS nl_sb,
    -- 提取AL联盟领袖信息
    MAX(CASE WHEN lgID = 'AL' AND sb_rank = 1 THEN playerID END) AS al_leader_id,
    MAX(CASE WHEN lgID = 'AL' AND sb_rank = 1 THEN SB END) AS al_sb
FROM (
    SELECT 
        yearID,
        lgID,
        playerID,
        SB,
        RANK() OVER (PARTITION BY yearID, lgID ORDER BY SB DESC) AS sb_rank
    FROM batting
) t
WHERE sb_rank = 1
GROUP BY yearID;

2. 横向连接（LATERAL JOIN）
   如果使用PostgreSQL、SQL Server这类支持横向连接的数据库，可以针对每个年份分别查询NL和AL的领袖，写法直观且无需多层嵌套：

SELECT 
    years.yearID,
    nl.playerID AS nl_leader_id,
    nl.SB AS nl_sb,
    al.playerID AS al_leader_id,
    al.SB AS al_sb
FROM (SELECT DISTINCT yearID FROM batting) years
LEFT JOIN LATERAL (
    SELECT playerID, SB
    FROM batting
    WHERE yearID = years.yearID AND lgID = 'NL'
    ORDER BY SB DESC
    -- 允许并列可改为 FETCH FIRST 1 ROWS WITH TIES
    LIMIT 1
) nl ON true
LEFT JOIN LATERAL (
    SELECT playerID, SB
    FROM batting
    WHERE yearID = years.yearID AND lgID = 'AL'
    ORDER BY SB DESC
    LIMIT 1
) al ON true;

注：若要求某年份必须两个联盟都有数据才显示，把LEFT JOIN换成INNER JOIN即可。

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