MySQL 5语法错误致SQL异常，求Java Spring环境下正确查询语句写法

首先，你的原SQL存在几个语法和逻辑上的问题：

• MySQL的普通SELECT语句里不能直接用IF...THEN...ENDIF这种流程控制语法（这是存储过程/函数专用的）
• MAX()函数内的嵌套子查询写法错误，没法直接把两个子查询相乘后塞进去
• 多表关联时缺少明确的过滤条件，容易产生笛卡尔积，导致结果不准确
• 重复的子查询会拖慢查询效率

结合你描述的业务需求（当买入日期<2018-01-31、卖出与买入日期差>365天、2018-01-31收盘价>买入价时，把买入价替换为该收盘价），我整理了修正后的SQL，用CASE WHEN来实现条件判断，同时优化了子查询避免重复计算：

SELECT
    CASE
        -- 满足三个业务条件时，用2018-01-31的收盘价计算最终成本
        WHEN buy_dates.buy_max_date < '2018-01-31'
             AND DATEDIFF(sell_dates.sell_max_date, buy_dates.buy_max_date) > 365
             AND stock_close.close_price > buy_info.buy_price THEN
            stock_close.close_price * sell_info.sell_shares
        -- 不满足条件时，沿用原买入价计算
        ELSE
            buy_info.buy_price * sell_info.sell_shares
    END AS acquisition_cost
FROM
    -- 先获取用户对应股票的最大买入日期
    (SELECT MAX(`DATE`) AS buy_max_date
     FROM nivpof_buystock
     WHERE sc_code = :company_code
       AND STATUS = 'Y'
       AND user_id = :userid) AS buy_dates,
    -- 获取用户对应股票的最大卖出日期
    (SELECT MAX(`DATE`) AS sell_max_date
     FROM nivpof_sellstock
     WHERE sc_code = :company_code
       AND STATUS = 'Y'
       AND user_id = :userid) AS sell_dates,
    -- 获取目标股票2018-01-31的收盘价
    (SELECT CLOSE_PRICE AS close_price
     FROM niv_companystockdetails
     WHERE sc_code = :company_code
       AND `DATE` = '2018-01-31') AS stock_close,
    -- 获取对应买入记录的价格
    (SELECT MAX(price) AS buy_price
     FROM nivpof_buystock
     WHERE P_ID = :sellpid) AS buy_info,
    -- 获取对应卖出记录的股数
    (SELECT no_of_shrs AS sell_shares
     FROM nivpof_sellstock
     WHERE p_id = :sellpid
       AND AUDIT_DATE_TIME = :udate) AS sell_info;

关键说明： #

• 用CASE WHEN替代原有的IF流程控制，完全符合MySQL普通查询的语法规范
• 将重复的统计子查询提前单独计算，既避免重复运算，也让逻辑更清晰
• 使用命名参数（:userid、:company_code等）适配Spring环境的参数绑定（你可以根据实际用的ORM框架调整格式，比如MyBatis用#{}）
• 明确每个子查询的过滤条件，避免不必要的数据关联，保证结果准确

如果你的需求是要更新表中的买入价（而不是仅仅查询计算值），可以用下面的UPDATE语句，完全匹配你给出的示例场景：

UPDATE nivpof_buystock bs
JOIN nivpof_sellstock ss ON bs.P_ID = ss.p_id
JOIN (SELECT MAX(`DATE`) AS buy_max_date
      FROM nivpof_buystock
      WHERE sc_code = :company_code
        AND STATUS = 'Y'
        AND user_id = :userid) AS buy_dates ON 1=1
JOIN (SELECT MAX(`DATE`) AS sell_max_date
      FROM nivpof_sellstock
      WHERE sc_code = :company_code
        AND STATUS = 'Y'
        AND user_id = :userid) AS sell_dates ON 1=1
JOIN (SELECT CLOSE_PRICE AS close_price
      FROM niv_companystockdetails
      WHERE sc_code = :company_code
        AND `DATE` = '2018-01-31') AS stock_close ON 1=1
SET bs.price = stock_close.close_price
WHERE bs.P_ID = :sellpid
  AND buy_dates.buy_max_date < '2018-01-31'
  AND DATEDIFF(sell_dates.sell_max_date, buy_dates.buy_max_date) > 365
  AND stock_close.close_price > bs.price;

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