如何在现有MySQL查询中新增prices表的最近日期价格列？

问题出在你直接LEFT JOIN了整个prices表——当一个账户对应的商品有多条符合日期条件的价格记录时，splits表的每一条交易拆分记录会和这些价格记录进行笛卡尔积匹配，导致计算sum()时同一条拆分记录被重复计算多次，最终数值异常。

要解决这个问题，我们需要先获取每个商品在2017-12-31之前的最新价格，再将这个单一结果和主查询关联，而不是直接连接整个prices表。这里提供两种无需临时表的实现方式：

方法1：使用关联子查询（嵌入SELECT子句） #

直接在SELECT字段里嵌套子查询，针对当前账户的商品ID获取最新价格：

SELECT 
    parent.name AS parentname, 
    a.name AS accname, 
    parent.code AS parentcode, 
    a.code AS acccode, 
    parent.guid AS parentguid, 
    a.guid AS accguid, 
    a.account_type AS accttype, 
    SUM(CASE WHEN DATE_FORMAT(post_date, '%Y-%m-%d') <= '2017-12-31' THEN (s.value_num/s.value_denom) ELSE 0 END) AS 'value2017-12-31', 
    SUM(CASE WHEN DATE_FORMAT(post_date, '%Y-%m-%d') <= '2018-01-25' THEN (s.value_num/s.value_denom) ELSE 0 END) AS 'value2018-01-25',
    -- 关联子查询获取对应商品的最新价格
    (SELECT 
        (p.value_num/p.value_denom) 
     FROM prices p 
     WHERE p.commodity_guid = a.commodity_guid 
       AND DATE_FORMAT(p.date, '%Y-%m-%d') <= '2017-12-31' 
     ORDER BY p.date DESC 
     LIMIT 1) AS 'price2017-12-31'
FROM transactions AS t 
INNER JOIN splits AS s ON s.tx_guid = t.guid 
INNER JOIN accounts AS a ON a.guid = s.account_guid 
INNER JOIN accounts AS parent ON parent.guid = a.parent_guid 
WHERE 
    a.hidden = 0 
    AND a.account_type NOT IN ('INCOME', 'EXPENSE') 
    AND parent.name <> '' 
    AND a.guid = '3f3fc442a98225f481bb72e0fd526cbb' 
GROUP BY accname, parentname 
ORDER BY acccode

方法2：使用窗口函数预筛选最新价格（适用于MySQL 8.0+） #

通过ROW_NUMBER()窗口函数给每个商品的价格按日期倒序编号，只保留编号为1的最新记录，再和主查询左连接：

WITH latest_prices AS (
    SELECT 
        commodity_guid,
        (value_num/value_denom) AS calcprice,
        ROW_NUMBER() OVER (PARTITION BY commodity_guid ORDER BY date DESC) AS rn
    FROM prices
    WHERE DATE_FORMAT(date, '%Y-%m-%d') <= '2017-12-31'
)
SELECT 
    parent.name AS parentname, 
    a.name AS accname, 
    parent.code AS parentcode, 
    a.code AS acccode, 
    parent.guid AS parentguid, 
    a.guid AS accguid, 
    a.account_type AS accttype, 
    SUM(CASE WHEN DATE_FORMAT(post_date, '%Y-%m-%d') <= '2017-12-31' THEN (s.value_num/s.value_denom) ELSE 0 END) AS 'value2017-12-31', 
    SUM(CASE WHEN DATE_FORMAT(post_date, '%Y-%m-%d') <= '2018-01-25' THEN (s.value_num/s.value_denom) ELSE 0 END) AS 'value2018-01-25',
    lp.calcprice AS 'price2017-12-31'
FROM transactions AS t 
INNER JOIN splits AS s ON s.tx_guid = t.guid 
INNER JOIN accounts AS a ON a.guid = s.account_guid 
INNER JOIN accounts AS parent ON parent.guid = a.parent_guid 
LEFT JOIN latest_prices lp ON lp.commodity_guid = a.commodity_guid AND lp.rn = 1
WHERE 
    a.hidden = 0 
    AND a.account_type NOT IN ('INCOME', 'EXPENSE') 
    AND parent.name <> '' 
    AND a.guid = '3f3fc442a98225f481bb72e0fd526cbb' 
GROUP BY accname, parentname, lp.calcprice -- MySQL 5.7+需将非聚合字段加入GROUP BY
ORDER BY acccode

两种方法都能保证每个账户只匹配到一条最新价格记录，彻底避免了笛卡尔积导致的sum值重复计算问题。

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