SQL新手求助：如何将两条SELECT查询语句合并为一条？

你的两条查询分别用于获取12月的零件明细数据，以及9月的成本汇总数据，以下两种方案可以将它们合并为单条查询：

方案一：用UNION ALL合并明细与汇总行 #

这种方式会把12月的明细行和9月的汇总行放在同一个结果集中，对应不上的列用NULL填充：

-- 12月零件明细数据
SELECT 
  part.Id, 
  part.Name, 
  part.Quantity, 
  part.Cost AS "Individual Part Cost", 
  Quantity * Cost AS "Total Part Cost",
  NULL AS "Total Month Cost",
  NULL AS "Average Month Cost"
FROM part
JOIN repair ON repair.Id = part.RepairId
WHERE MONTHNAME(repair.Date) = "December"

UNION ALL

-- 9月成本汇总数据
SELECT 
  NULL, 
  NULL, 
  NULL, 
  NULL, 
  NULL,
  SUM(Cost) AS "Total Month Cost",
  AVG(Cost) AS "Average Month Cost"
FROM part
JOIN repair ON repair.Id = part.RepairId
WHERE MONTHNAME(repair.Date) = "September"

注：这里把你原来的隐式逗号连接改成了显式JOIN，SQL语法更规范，可读性更强。

方案二：在明细行中附带另一月份的汇总数据 #

如果希望每条12月的明细数据旁都显示9月的成本汇总，可以用子查询实现：

SELECT 
  part.Id, 
  part.Name, 
  part.Quantity, 
  part.Cost AS "Individual Part Cost", 
  Quantity * Cost AS "Total Part Cost",
  -- 子查询获取9月总成本
  (SELECT SUM(Cost) FROM part JOIN repair ON repair.Id = part.RepairId WHERE MONTHNAME(repair.Date) = "September") AS "Total Month Cost",
  -- 子查询获取9月平均成本
  (SELECT AVG(Cost) FROM part JOIN repair ON repair.Id = part.RepairId WHERE MONTHNAME(repair.Date) = "September") AS "Average Month Cost"
FROM part
JOIN repair ON repair.Id = part.RepairId
WHERE MONTHNAME(repair.Date) = "December"

补充：若需求是查询同一月份的明细+汇总 #

如果是笔误，实际需要的是同一月份（比如都是12月）的明细数据和对应汇总，可以用窗口函数更高效地实现：

SELECT 
  part.Id, 
  part.Name, 
  part.Quantity, 
  part.Cost AS "Individual Part Cost", 
  Quantity * Cost AS "Total Part Cost",
  -- 窗口函数计算该月总成本
  SUM(Cost) OVER() AS "Total Month Cost",
  -- 窗口函数计算该月平均成本
  AVG(Cost) OVER() AS "Average Month Cost"
FROM part
JOIN repair ON repair.Id = part.RepairId
WHERE MONTHNAME(repair.Date) = "December"

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