MySQL同表两个查询合并为单行，UNION写法报错如何解决？

问题原因 #

• 语法错误：MySQL中如果要对UNION两侧的每个子查询单独应用ORDER BY和LIMIT，必须给每个子查询套上括号，否则解析器会将子查询内的ORDER BY识别为作用于整个UNION结果集，直接引发语法报错。
• 逻辑错误：UNION的作用是将多个查询的结果纵向拼接，即使你修正语法后执行，得到的也是2行1列的结果，完全不符合你需要的1行2列（期初、期末各为单独列）的需求。

正确修复写法 #

直接将两个独立的标量子查询作为SELECT的返回列即可，不需要使用UNION，代码如下：

SELECT
  -- 期初值子查询
  (SELECT IF(o.action = "Buy", o.transMarketGross, o.transMarketNet)
   FROM `Order` o
   WHERE o.agentId = (SELECT id FROM Agent WHERE owner = "Alexander")
     AND o.dateTimeExecuted <= DATE_SUB(curdate(), INTERVAL 1 MONTH)
   ORDER BY o.dateTimeExecuted DESC LIMIT 1) AS startValue,
  -- 期末值子查询
  (SELECT IF(o.action = "Buy", o.transMarketGross, o.transMarketNet)
   FROM `Order` o
   WHERE o.agentId = (SELECT id FROM Agent WHERE owner = "Alexander")
   ORDER BY o.dateTimeExecuted DESC LIMIT 1) AS endValue;

该写法会直接返回1行2列的结果，完全匹配需求。

优化方案 #

优化1：复用Agent查询结果，减少重复子查询 #

如果支持使用用户变量，可以先缓存Alexander对应的agentId，避免重复执行Agent表的子查询：

SET @targetAgentId = (SELECT id FROM Agent WHERE owner = "Alexander");
SELECT
  (SELECT IF(o.action = "Buy", o.transMarketGross, o.transMarketNet)
   FROM `Order` o
   WHERE o.agentId = @targetAgentId
     AND o.dateTimeExecuted <= DATE_SUB(curdate(), INTERVAL 1 MONTH)
   ORDER BY o.dateTimeExecuted DESC LIMIT 1) AS startValue,
  (SELECT IF(o.action = "Buy", o.transMarketGross, o.transMarketNet)
   FROM `Order` o
   WHERE o.agentId = @targetAgentId
   ORDER BY o.dateTimeExecuted DESC LIMIT 1) AS endValue;

优化2：单次扫描Order表获取两个值（适用于MySQL8.0+、MariaDB10.2+等支持窗口函数的版本） #

通过窗口函数仅扫描一次Order表就能同时算出处初和期末值，在订单数据量大时性能提升明显：

WITH sorted_orders AS (
  SELECT
    IF(o.action = "Buy", o.transMarketGross, o.transMarketNet) AS val,
    o.dateTimeExecuted,
    -- 所有订单按时间倒序排名，排名1为最新订单即期末值
    ROW_NUMBER() OVER (ORDER BY o.dateTimeExecuted DESC) AS rn_all,
    -- 符合期初时间要求的订单按时间倒序排名，排名1为期初值
    ROW_NUMBER() OVER (
      PARTITION BY IF(o.dateTimeExecuted <= DATE_SUB(curdate(), INTERVAL 1 MONTH), 1, 0)
      ORDER BY o.dateTimeExecuted DESC
    ) AS rn_period
  FROM `Order` o
  WHERE o.agentId = (SELECT id FROM Agent WHERE owner = "Alexander")
)
SELECT
  MAX(IF(rn_period = 1 AND dateTimeExecuted <= DATE_SUB(curdate(), INTERVAL 1 MONTH), val, NULL)) AS startValue,
  MAX(IF(rn_all = 1, val, NULL)) AS endValue
FROM sorted_orders;

内容的提问来源于stack exchange，提问作者A. Vreeswijk