如何合并两个独立运行正常的MySQL目标与业绩统计查询？

嘿，针对你这两个都返回单行结果的查询（同门店、同月份），有几个简单靠谱的合并方式，挑适合你的来用：

方法1：SELECT子查询直接嵌入（最直观） #

这种方式把每个查询作为字段嵌入主查询里，非常适合单条结果的场景，代码简洁易懂：

SELECT
  (SELECT sum(monthly_target) FROM `tbl_goal` inner join user on tbl_goal.uid=user.id where user.store=1 and month='February') as month_target,
  (SELECT sum(net) FROM `data` inner join user on data.uid=user.id where user.store=1 and month='February') as achieved,
  (SELECT sum(hairs_total) FROM `data` inner join user on data.uid=user.id where user.store=1 and month='February') as hairs_total,
  (SELECT sum(beard_total) FROM `data` inner join user on data.uid=user.id where user.store=1 and month='February') as beard_total,
  (SELECT sum(product_total) FROM `data` inner join user on data.uid=user.id where user.store=1 and month='February') as product_total;

或者为了避免重复写data表的关联条件，可以把第二个查询先聚合好再作为子查询：

SELECT
  gt.month_target,
  dt.achieved,
  dt.hairs_total,
  dt.beard_total,
  dt.product_total
FROM
  (SELECT sum(monthly_target) as month_target FROM `tbl_goal` inner join user on tbl_goal.uid=user.id where user.store=1 and month='February') gt,
  (SELECT sum(net) as achieved, sum(hairs_total) as hairs_total, sum(beard_total) as beard_total, sum(product_total) as product_total FROM `data` inner join user on data.uid=user.id where user.store=1 and month='February') dt;

方法2：CROSS JOIN交叉连接 #

因为两个子查询都只返回一行数据，交叉连接会直接把它们的字段合并成一行，效果和上面的第二种写法一致，只是语法更明确：

SELECT
  gt.month_target,
  dt.achieved,
  dt.hairs_total,
  dt.beard_total,
  dt.product_total
FROM
  (SELECT sum(monthly_target) as month_target FROM `tbl_goal` inner join user on tbl_goal.uid=user.id where user.store=1 and month='February') gt
CROSS JOIN
  (SELECT sum(net) as achieved, sum(hairs_total) as hairs_total, sum(beard_total) as beard_total, sum(product_total) as product_total FROM `data` inner join user on data.uid=user.id where user.store=1 and month='February') dt;

方法3：带关联条件的LEFT JOIN（扩展性更强） #

如果以后你需要查询多个门店或多个月份的数据，这种方法更灵活——通过store和month字段关联两个子查询的结果，确保同门店同月份的数据对应上：

SELECT
  gt.store,
  gt.month,
  gt.month_target,
  dt.achieved,
  dt.hairs_total,
  dt.beard_total,
  dt.product_total
FROM
  (SELECT user.store, tbl_goal.month, sum(monthly_target) as month_target FROM `tbl_goal` inner join user on tbl_goal.uid=user.id where user.store=1 and month='February' GROUP BY user.store, tbl_goal.month) gt
LEFT JOIN
  (SELECT user.store, data.month, sum(net) as achieved, sum(hairs_total) as hairs_total, sum(beard_total) as beard_total, sum(product_total) as product_total FROM `data` inner join user on data.uid=user.id where user.store=1 and month='February' GROUP BY user.store, data.month) dt
ON gt.store = dt.store AND gt.month = dt.month;

这种写法即使某个门店/月份只有目标数据或只有业绩数据，也能正常返回结果（缺失的字段会显示为NULL），适合未来扩展查询范围的场景。

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