如何解决SQL查询结果行重复及列值匹配错误问题

以下是针对你遇到的重复行、列值错误问题的常见修正思路：

常见错误场景及修正方法 #

1. 无关联条件导致笛卡尔积（最常见） #

如果你的查询是将同一张股票表分别取IBM和GLD数据，但没添加日期关联，会导致每行IBM数据和每行GLD数据都匹配，产生重复行且日期对应错误。

错误示例：

SELECT 
    t1.Date,
    t1.Close AS IBM_Close,
    t2.Close AS GLD_Close
FROM stock_prices t1, stock_prices t2
WHERE t1.Symbol = 'IBM' AND t2.Symbol = 'GLD'

修正后：添加日期等值关联，确保同日期的收盘价匹配

SELECT 
    t1.Date,
    t1.Close AS IBM_Close,
    t2.Close AS GLD_Close
FROM stock_prices t1
INNER JOIN stock_prices t2 
    ON t1.Date = t2.Date
WHERE t1.Symbol = 'IBM' AND t2.Symbol = 'GLD'

2. 条件聚合未分组 #

如果用CASE语句拆分收盘价，但忘记按日期分组，会导致结果混乱或重复。

错误示例：

SELECT 
    Date,
    MAX(CASE WHEN Symbol = 'IBM' THEN Close END) AS IBM_Close,
    MAX(CASE WHEN Symbol = 'GLD' THEN Close END) AS GLD_Close
FROM stock_prices
WHERE Symbol IN ('IBM', 'GLD')
-- 缺少GROUP BY Date，无法按日期聚合

修正后：添加GROUP BY Date，确保每个日期只返回一行

SELECT 
    Date,
    MAX(CASE WHEN Symbol = 'IBM' THEN Close END) AS IBM_Close,
    MAX(CASE WHEN Symbol = 'GLD' THEN Close END) AS GLD_Close
FROM stock_prices
WHERE Symbol IN ('IBM', 'GLD')
GROUP BY Date
ORDER BY Date

3. 原表存在重复的日期-符号记录 #

如果原表中同一日期、同一股票有多条收盘价记录，需要先清理重复数据，再关联查询。

修正方案：先用CTE清理重复记录，再关联

WITH cleaned_stocks AS (
    SELECT 
        Date, 
        Symbol, 
        MAX(Close) AS Close -- 根据业务需求选择MAX/MIN/LAST_VALUE等
    FROM stock_prices
    WHERE Symbol IN ('IBM', 'GLD')
    GROUP BY Date, Symbol
)
SELECT 
    t1.Date,
    t1.Close AS IBM_Close,
    t2.Close AS GLD_Close
FROM cleaned_stocks t1
INNER JOIN cleaned_stocks t2 
    ON t1.Date = t2.Date
WHERE t1.Symbol = 'IBM' AND t2.Symbol = 'GLD'

关键注意点 #

• 所有关联查询必须包含日期相等的条件，避免无意义的笛卡尔积
• 使用条件聚合拆分多列时，必须按Date分组
• 先检查原表是否存在重复的日期-股票记录，必要时先清理

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