MySQL SELECT语句HAVING/WHERE问题：多instrument时查询结果异常

表结构 #

我有3张表：

• patrimoine_instruments(instrument_id, instrument, instrument_ISIN)
• patrimoine_transactions(transaction_id, instrument, quantite, valeur)
• quote(quote, quote_ISIN, close_datetime, last)

查询需求 #

需要获取截至今日的投资组合数据，满足：

• 列出所有quantite不为0的instrument
• 显示每个instrument对应的总quantite
• 显示每个instrument在quote表中的最新last值
• 显示每个instrument在quote表中的最新close_datetime

原查询问题 #

以下SQL仅在quote表中只有单个instrument的数据时运行正常：

SELECT 
patrimoine_instruments.`instrument` as actif, 
SUM(DISTINCT patrimoine_transactions.`quantite`) as quantite, 
(SELECT quote.last HAVING close_datetime = MAX(close_datetime) AND patrimoine_instruments.instrument_ISIN = quote.quote_ISIN) as valeur,
MAX(quote.close_datetime) as date
FROM `patrimoine_transactions`
LEFT JOIN patrimoine_instruments ON patrimoine_transactions.instrument = patrimoine_instruments.instrument_id
LEFT JOIN quote ON patrimoine_instruments.instrument_ISIN = quote.quote_ISIN
GROUP BY patrimoine_instruments.`instrument`
HAVING SUM(`quantite`) <> 0  
ORDER BY `patrimoine_instruments`.`instrument` ASC;

当quote表存在多个instrument的数据时，返回结果错误——原因是子查询未正确限定当前instrument的ISIN，且关联quote表时引入多条记录导致聚合计算异常。

解决方案 #

先预查询每个quote_ISIN对应的最新报价记录，再与投资组合数据关联，确保每个instrument只匹配一条最新报价：

SELECT 
    pi.`instrument` AS actif,
    SUM(pt.`quantite`) AS quantite,
    q.`last` AS valeur,
    q.`close_datetime` AS date
FROM `patrimoine_transactions` pt
LEFT JOIN `patrimoine_instruments` pi 
    ON pt.instrument = pi.instrument_id
LEFT JOIN (
    -- 筛选每个ISIN的最新报价
    SELECT 
        quote_ISIN,
        last,
        close_datetime
    FROM quote
    WHERE (quote_ISIN, close_datetime) IN (
        SELECT quote_ISIN, MAX(close_datetime) 
        FROM quote 
        GROUP BY quote_ISIN
    )
) q 
    ON pi.instrument_ISIN = q.quote_ISIN
GROUP BY pi.`instrument`, q.`last`, q.`close_datetime`
HAVING SUM(pt.`quantite`) <> 0
ORDER BY pi.`instrument` ASC;

关键调整说明 #

1. 子查询先通过分组获取每个ISIN的最新时间，再筛选对应报价，避免多对多关联导致的数据重复。
2. 移除原查询中SUM(DISTINCT quantite)的DISTINCT，确保交易数量求和准确。
3. 主查询分组时包含报价字段，避免因分组规则导致的字段聚合错误。

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