mysql - 分组依据和聚合函数

Question

我尝试将 union 和 group by 与聚合函数结合使用，但所有尝试要么导致行丢失，要么数据不正确。下面是两个生成我需要的数据的查询，现在我只需要将它们联合起来

这是 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 |
+----------+------------+------------+

最后，这是我在阅读有关堆栈溢出的其他答案后尝试使用 union 的尝试:

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 |
+----------+------------+------------+

Answer 1

您的分组依据是正确的......但我认为您不想要工会。试试这个:

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 结果中的记录(相反的情况)。 FULL JOIN 可以做到这一点，但我的理解是这些没有在 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