MySQL：如何利用SELECT中子查询字段的别名进行求和运算

这个报错很常见，原因是SQL的执行顺序限制：数据库在处理SELECT子句里的字段时，还没完成对别名（比如product1_shift1）的解析，所以你不能在同一个SELECT里直接用这些别名来做计算。下面给你几种可行的解决方案，其中第三种还能优化你的查询性能：

方案1：用子查询包裹原查询，在外层计算总和 #

把你的原查询作为一个子查询，在外层SELECT里就可以直接引用这些别名进行相加了：

SELECT 
  day, 
  date, 
  product1_shift1, 
  product1_shift2, 
  product1_shift3,
  COALESCE(product1_shift1, 0) + COALESCE(product1_shift2, 0) + COALESCE(product1_shift3, 0) AS product1_total
FROM (
  SELECT 
    DAYNAME(date) AS day, 
    DATE_FORMAT(date, "%d-%m-%Y") AS date, 
    (SELECT SUM(qty) FROM bill_details JOIN bills ON bills.bill_id=bill_details.bill_id WHERE bills.shift_id='48c73e3d-57de-4fea-aae4-7222d7e6afed' AND bills.date=bills_master.date) AS product1_shift1, 
    (SELECT SUM(qty) FROM bill_details JOIN bills ON bills.bill_id=bill_details.bill_id WHERE bills.shift_id='463ec995-cfad-46ff-8e65-3115e6e651c5' AND bills.date=bills_master.date) AS product1_shift2, 
    (SELECT SUM(qty) FROM bill_details JOIN bills ON bills.bill_id=bill_details.bill_id WHERE bills.shift_id='0a792c5f-84a6-4534-818c-427cfe8cd3ca' AND bills.date=bills_master.date) AS product1_shift3 
  FROM bills bills_master 
  GROUP BY bills_master.date
) AS subquery;

注：用COALESCE把可能的NULL值转为0，避免某个班次无数据时总和变成NULL

方案2：使用CTE（公共表表达式） #

如果你的数据库支持CTE（比如MySQL 8.0+、PostgreSQL、SQL Server等），可以用CTE来简化结构，可读性更好：

WITH daily_shifts AS (
  SELECT 
    DAYNAME(date) AS day, 
    DATE_FORMAT(date, "%d-%m-%Y") AS date, 
    (SELECT SUM(qty) FROM bill_details JOIN bills ON bills.bill_id=bill_details.bill_id WHERE bills.shift_id='48c73e3d-57de-4fea-aae4-7222d7e6afed' AND bills.date=bills_master.date) AS product1_shift1, 
    (SELECT SUM(qty) FROM bill_details JOIN bills ON bills.bill_id=bill_details.bill_id WHERE bills.shift_id='463ec995-cfad-46ff-8e65-3115e6e651c5' AND bills.date=bills_master.date) AS product1_shift2, 
    (SELECT SUM(qty) FROM bill_details JOIN bills ON bills.bill_id=bill_details.bill_id WHERE bills.shift_id='0a792c5f-84a6-4534-818c-427cfe8cd3ca' AND bills.date=bills_master.date) AS product1_shift3 
  FROM bills bills_master 
  GROUP BY bills_master.date
)
SELECT 
  day, 
  date, 
  product1_shift1, 
  product1_shift2, 
  product1_shift3,
  COALESCE(product1_shift1, 0) + COALESCE(product1_shift2, 0) + COALESCE(product1_shift3, 0) AS product1_total
FROM daily_shifts;

方案3：优化查询，用条件聚合替代多个子查询（推荐） #

你的原查询里三个子查询逻辑几乎一样，只是shift_id不同，多次子查询会重复关联表，影响性能。可以用条件SUM一次性计算出三个班次的数量，然后直接相加，这样只需要一次表关联：

SELECT 
  DAYNAME(bm.date) AS day, 
  DATE_FORMAT(bm.date, "%d-%m-%Y") AS date, 
  SUM(CASE WHEN b.shift_id='48c73e3d-57de-4fea-aae4-7222d7e6afed' THEN bd.qty ELSE 0 END) AS product1_shift1,
  SUM(CASE WHEN b.shift_id='463ec995-cfad-46ff-8e65-3115e6e651c5' THEN bd.qty ELSE 0 END) AS product1_shift2,
  SUM(CASE WHEN b.shift_id='0a792c5f-84a6-4534-818c-427cfe8cd3ca' THEN bd.qty ELSE 0 END) AS product1_shift3,
  SUM(bd.qty) AS product1_total -- 直接计算所有目标班次的总和，和上面三个字段相加效果一致
FROM bills bm
JOIN bills b ON b.date = bm.date
JOIN bill_details bd ON b.bill_id = bd.bill_id
WHERE b.shift_id IN ('48c73e3d-57de-4fea-aae4-7222d7e6afed', '463ec995-cfad-46ff-8e65-3115e6e651c5', '0a792c5f-84a6-4534-818c-427cfe8cd3ca')
GROUP BY bm.date;

这种方法减少了重复的子查询，执行效率会比前两种高不少，同时也避免了NULL值的问题（因为CASE里默认返回0）。

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