连接含重复值与唯一值列时获取唯一记录的SQL查询方案

我明白你现在的困扰——内连接后出现了重复记录，导致Cube没法处理这个查询。问题根源应该是Fact_Monthly_Policy_Snap表（毕竟Dim_Geography的Geography_Identifier是唯一主键）或者Dim_Risk表里存在重复的Risk_ID+Insurance_Score组合，让连接后的结果产生了冗余行。下面给你两种靠谱的解决方案：

方案1：用DISTINCT快速去重 #

如果你的查询结果里存在完全重复的行（所有字段值都一模一样），直接在SELECT后加DISTINCT关键字就能让数据库自动过滤重复项，这是最简洁的处理方式：

SELECT DISTINCT
    s.Geography_Identifier,
    s.State_Code,
    s.State_Name,
    s.County_Name,
    s.City_Name,
    s.ZIP_Code,
    a.Risk_ID,
    a.Risk_Address,
    a.Latitude,
    a.Longitude,
    a.Distance_to_Coast,
    a.Insurance_Score
FROM [Policy].[Dim_Risk] AS a
INNER JOIN [Policy].[Fact_Monthly_Policy_Snap] AS b 
    ON b.Risk_ID = a.Risk_ID 
    AND b.Insurance_score = a.Insurance_Score
INNER JOIN [Common].[Dim_Geography] AS s 
    ON b.Risk_Geo_ID = s.Geography_Identifier

方案2：用窗口函数精准控制去重（更灵活） #

如果结果里的重复行只是部分字段重复（比如同一个Risk_ID对应多条不同时间的快照记录，但你只需要取最新的那一条），用ROW_NUMBER()窗口函数会更可控。假设Fact_Monthly_Policy_Snap有Snapshot_Date字段，我们可以按Risk_ID分组，取每个组内最新的快照记录：

WITH RankedRisks AS (
    SELECT
        s.Geography_Identifier,
        s.State_Code,
        s.State_Name,
        s.County_Name,
        s.City_Name,
        s.ZIP_Code,
        a.Risk_ID,
        a.Risk_Address,
        a.Latitude,
        a.Longitude,
        a.Distance_to_Coast,
        a.Insurance_Score,
        -- 按Risk_ID分组，按快照日期倒序排名，最新的记录排第1
        ROW_NUMBER() OVER (PARTITION BY a.Risk_ID ORDER BY b.Snapshot_Date DESC) AS RowNum
    FROM [Policy].[Dim_Risk] AS a
    INNER JOIN [Policy].[Fact_Monthly_Policy_Snap] AS b 
        ON b.Risk_ID = a.Risk_ID 
        AND b.Insurance_score = a.Insurance_Score
    INNER JOIN [Common].[Dim_Geography] AS s 
        ON b.Risk_Geo_ID = s.Geography_Identifier
)
SELECT 
    Geography_Identifier,
    State_Code,
    State_Name,
    County_Name,
    City_Name,
    ZIP_Code,
    Risk_ID,
    Risk_Address,
    Latitude,
    Longitude,
    Distance_to_Coast,
    Insurance_Score
FROM RankedRisks
WHERE RowNum = 1 -- 只保留每个Risk_ID对应的第一条记录

额外排查小技巧 #

如果不确定重复行到底来自哪张表，可以先运行下面的查询定位问题：

-- 检查Dim_Risk里是否有重复的Risk_ID+Insurance_Score组合
SELECT Risk_ID, Insurance_Score, COUNT(*) AS RowCount
FROM [Policy].[Dim_Risk]
GROUP BY Risk_ID, Insurance_Score
HAVING COUNT(*) > 1;

-- 检查Fact_Monthly_Policy_Snap里是否有重复的Risk_ID+Insurance_Score组合
SELECT Risk_ID, Insurance_Score, COUNT(*) AS RowCount
FROM [Policy].[Fact_Monthly_Policy_Snap]
GROUP BY Risk_ID, Insurance_Score
HAVING COUNT(*) > 1;

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