我正在尝试对聚合函数使用联合和分组依据，但是所有尝试都会导致行丢失或数据不正确。以下是产生我所需数据的两个查询，现在我只需要将它们合并
这是pastebin上的示例SQL转储
SQL查询＃1：
SELECT o.id as order_id, SUM(price * quantity) as total_prod, 0 as total_inst
FROM orders as o, orders_product_line as opl, products as p
WHERE opl.order_id = o.id
AND p.id = opl.product_id
GROUP BY o.id
查询1的结果
+----------+------------+------------+
| order_id | total_prod | total_inst |
+----------+------------+------------+
| 1 | 4200 | 0 |
| 2 | 40000 | 0 |
| 3 | 3600000 | 0 |
| 4 | 44500 | 0 |
| 5 | 1229800 | 0 |
| 6 | 45000000 | 0 |
+----------+------------+------------+
SQL查询2：
SELECT o.id as order_id, 0 as total_prod, SUM(rate * hours) as total_inst
FROM orders as o, orders_installation_line as oil, installations as i
WHERE oil.order_id = o.id
AND i.id = oil.intallation_id
GROUP BY order_id
查询2的结果：
+----------+------------+------------+
| order_id | total_prod | total_inst |
+----------+------------+------------+
| 1 | 0 | 4675 |
| 2 | 0 | 255000 |
| 3 | 0 | 18880 |
| 4 | 0 | 600 |
| 5 | 0 | 3540 |
+----------+------------+------------+
这是我尝试对两个表使用并集
SELECT o.id as order_id, SUM(price * quantity) as total_prod, 0 as total_inst FROM orders as o, orders_product_line as opl, products as p WHERE opl.order_id = o.id AND p.id = opl.product_id GROUP BY o.id
UNION ALL
SELECT o.id as order_id, 0 as total_prod, SUM(rate * hours) as total_inst FROM orders as o, orders_installation_line as oil, installations as i WHERE oil.order_id = o.id AND i.id = oil.intallation_id GROUP BY order_id
结果
+----------+------------+------------+
| order_id | total_prod | total_inst |
+----------+------------+------------+
| 1 | 4200 | 0 |
| 2 | 40000 | 0 |
| 3 | 3600000 | 0 |
| 4 | 44500 | 0 |
| 5 | 1229800 | 0 |
| 6 | 45000000 | 0 |
| 1 | 0 | 4675 |
| 2 | 0 | 255000 |
| 3 | 0 | 18880 |
| 4 | 0 | 600 |
| 5 | 0 | 3540 |
+----------+------------+------------+
最后，这是我在阅读堆栈溢出的其他答案后尝试使用并集的方法：
SELECT *
FROM
(
SELECT o.id as order_id, SUM(price * quantity) as total_prod, 0 as total_inst
FROM orders as o, orders_product_line as opl, products as p
WHERE opl.order_id = o.id
AND p.id = opl.product_id
GROUP BY o.id
UNION ALL
SELECT o.id as order_id, 0 as total_prod, SUM(rate * hours) as total_inst
FROM orders as o, orders_installation_line as oil, installations as i
WHERE oil.order_id = o.id
AND i.id = oil.intallation_id
GROUP BY order_id
) Q
GROUP BY Q.order_id
最后的结合的结果是：
+----------+------------+------------+
| order_id | total_prod | total_inst |
+----------+------------+------------+
| 1 | 4200 | 0 |
| 2 | 40000 | 0 |
| 3 | 3600000 | 0 |
| 4 | 44500 | 0 |
| 5 | 1229800 | 0 |
| 6 | 45000000 | 0 |
+----------+------------+------------+
我想念什么？我需要最后一列
total_inst来显示值。这是我要寻找的结果：+----------+------------+------------+
| order_id | total_prod | total_inst |
+----------+------------+------------+
| 1 | 4200 | 4675 |
| 2 | 40000 | 255000 |
| 3 | 3600000 | 18880 |
| 4 | 44500 | 600 |
| 5 | 1229800 | 3540 |
| 6 | 45000000 | 0 |
+----------+------------+------------+
最佳答案
您的分组依据是正确的……但是我不认为您想要一个工会。尝试以下方法：
select q1.order_id, q1.total_prod, q2.total_inst from
(SELECT o.id as order_id, SUM(price * quantity) as total_prod, 0 as total_inst FROM orders as o, orders_product_line as opl, products as p WHERE opl.order_id = o.id AND p.id = opl.product_id GROUP BY o.id) q1,
(SELECT o.id as order_id, 0 as total_prod, SUM(rate * hours) as total_inst FROM orders as o, orders_installation_line as oil, installations as i WHERE oil.order_id = o.id AND i.id = oil.intallation_id GROUP BY order_id) q2
where q1.order_id = q2.order_id
编辑：上面的查询将只检索两个内部查询返回的order_ids。要包含order_id 6，您需要一个LEFT JOIN。但是，那将不会获得可能来自q2的结果中但不存在于q1的结果中的记录（相反的情况）。完全联接可以做到这一点，但是我的理解是这些不是在MySQL中实现的。但是，它们可以被模拟，如下所示：Full Outer Join in MySQL
select q1.order_id, q1.total_prod, q2.total_inst from
(SELECT o.id as order_id, SUM(price * quantity) as total_prod, 0 as total_inst FROM orders as o, orders_product_line as opl, products as p WHERE opl.order_id = o.id AND p.id = opl.product_id GROUP BY o.id) q1
LEFT JOIN
(SELECT o.id as order_id, 0 as total_prod, SUM(rate * hours) as total_inst FROM orders as o, orders_installation_line as oil, installations as i WHERE oil.order_id = o.id AND i.id = oil.intallation_id GROUP BY order_id) q2
ON q1.order_id = q2.order_id
UNION
select q1.order_id, q1.total_prod, q2.total_inst from
(SELECT o.id as order_id, SUM(price * quantity) as total_prod, 0 as total_inst FROM orders as o, orders_product_line as opl, products as p WHERE opl.order_id = o.id AND p.id = opl.product_id GROUP BY o.id) q1
RIGHT JOIN
(SELECT o.id as order_id, 0 as total_prod, SUM(rate * hours) as total_inst FROM orders as o, orders_installation_line as oil, installations as i WHERE oil.order_id = o.id AND i.id = oil.intallation_id GROUP BY order_id) q2
ON q1.order_id = q2.order_id
最终编辑：这是另一种方法的有效SQLFiddle。 http://sqlfiddle.com/#!2/deff12/12/0
关于mysql - 按功能分组并汇总，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20484737/