SQL使用GROUP BY+MIN及INNER JOIN时关联字段取值错误如何解决

你遇到的问题本质是SQL非严格GROUP BY模式的特性：当你只对tt.card_number分组，其余非聚合字段（比如tcg.url）会随机取分组内的第一条匹配记录，和你用MIN()取到的最低价格没有关联。以下是两种常用解决方案：

方案1：使用窗口函数（推荐，适配MySQL 8.0+、PostgreSQL、SQL Server等主流数据库） #

用ROW_NUMBER()窗口函数给每个card_number分组下的记录按价格升序排序，取排序第一的就是最低价格对应的完整记录：

SELECT name, card_number, deck_count, url, marketPrice as price
FROM (
    SELECT 
        cd.name, 
        tt.card_number, 
        tt.deck_count, 
        tcg.url, 
        tcgp.marketPrice,
        -- 按卡片分组，价格升序排序，最低价格的行rn=1
        ROW_NUMBER() OVER (PARTITION BY tt.card_number ORDER BY tcgp.marketPrice ASC) as rn
    FROM card_occurrences tt
    INNER JOIN card_database cd ON tt.card_number = cd.id
    INNER JOIN tcgplayer_cards tcg ON cd.name = tcg.name
    INNER JOIN tcgplayer_set_prices tcgp ON tcgp.productId = tcg.productID
) t
WHERE rn = 1
ORDER BY deck_count DESC LIMIT 100;

如果同一张卡有多个相同最低价格的记录需要全部返回，把ROW_NUMBER()换成RANK()即可。

方案2：子查询关联适配（兼容MySQL 5.x等不支持窗口函数的环境） #

先通过子查询拿到每个卡片对应的最低价格，再二次关联价格表匹配到对应最低价格的记录：

SELECT 
    cd.name, 
    tt.card_number, 
    tt.deck_count, 
    tcg.url, 
    min_price.price
FROM card_occurrences tt
INNER JOIN card_database cd ON tt.card_number = cd.id
INNER JOIN tcgplayer_cards tcg ON cd.name = tcg.name
-- 先关联子查询拿到每个商品ID对应的最低价格
INNER JOIN (
    SELECT tcg.productId, MIN(tcgp.marketPrice) as price
    FROM tcgplayer_cards tcg
    INNER JOIN tcgplayer_set_prices tcgp ON tcgp.productId = tcg.productID
    GROUP BY tcg.productId
) min_price ON min_price.productId = tcg.productID
-- 再关联价格表匹配到对应最低价格的记录
INNER JOIN tcgplayer_set_prices tcgp ON tcgp.productId = tcg.productID AND tcgp.marketPrice = min_price.price
GROUP BY tt.card_number
ORDER BY tt.deck_count DESC LIMIT 100;

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