Left Join结果异常：需为Table1添加Table2的City统计列

原查询存在的问题 #

• 关联字段拼写错误：JOIN条件中Table1.County_Name = Table2.Count_Name存在笔误，Table2的字段名应为County_Name，少写字母"y"会导致关联逻辑失效，无法匹配对应区县的数据。
• 重复计数问题：直接通过Left Join关联后使用窗口函数统计，会因Table2中一个区县对应多个城市，导致Table1的行被重复展开，窗口函数最终统计的是展开后的行数而非实际城市数量，结果会偏大。

修正方案一：预聚合Table2后关联（推荐，性能更优） #

先对Table2按区县和类型分组统计城市数量，再与Table1关联，避免重复行产生：

SELECT 
    t1.County
    , t1.County_Name
    , t1.Type
    , t1.Date
    , t1.Value
    , COALESCE(t2.City_Count_Base, 0) AS City_Count_Base
    , COALESCE(t2.City_Count_NonBase, 0) AS City_Count_NonBase
FROM Table1 t1
LEFT JOIN (
    -- 预聚合统计各区县+类型下的两类城市数量
    SELECT 
        County_Name
        , Type
        , COUNT(CASE WHEN Type = 'BASE' THEN City END) AS City_Count_Base
        , COUNT(CASE WHEN Type = 'NON-BASE' THEN City END) AS City_Count_NonBase
    FROM Table2
    GROUP BY County_Name, Type
) t2 ON t1.County_Name = t2.County_Name 
    AND t1.Type = t2.Type

修正方案二：修正关联字段+窗口函数去重 #

若坚持使用窗口函数，需修正字段拼写并通过DISTINCT避免重复计数：

SELECT 
    County
    , Table1.County_Name
    , Table1.Type
    , Date
    , Value
    , COUNT(DISTINCT CASE WHEN Table2.Type = 'BASE' THEN Table2.City END) OVER (PARTITION BY Table1.County_Name, Table1.Type) AS City_Count_Base
    , COUNT(DISTINCT CASE WHEN Table2.Type = 'NON-BASE' THEN Table2.City END) OVER (PARTITION BY Table1.County_Name, Table1.Type) AS City_Count_NonBase
FROM Table1
LEFT JOIN Table2 ON Table1.County_Name = Table2.County_Name  -- 修正字段拼写错误
    AND Table1.Type = Table2.Type

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