mysql - GROUP BY 关于 ORDER BY 的问题

Question

我有一个显示订单、代理和团队的表格。我唯一的问题是按 order_id 按升序排列顺序。我在关联 ORDER BY 语句时遇到问题。

FROM ((((line_item
INNER JOIN order_ ON line_item.order_id = order_.order_id)
INNER JOIN sales_agent ON order_.agent_id = sales_agent.agent_id)
INNER JOIN sales_team ON sales_agent.team_id  = sales_team.team_id)
INNER JOIN product ON line_item.product_id = product.product_id)
GROUP BY order_.order_id


UNION ALL SELECT '', '', '',
(
    SELECT 
    SUM(product.price * line_item.quantity)
    FROM ((((line_item
    INNER JOIN order_ ON line_item.order_id = order_.order_id)
    INNER JOIN sales_agent ON order_.agent_id = sales_agent.agent_id)
    INNER JOIN sales_team ON sales_agent.team_id  = sales_team.team_id)
    INNER JOIN product ON line_item.product_id = product.product_id)
);

这是实际的输出：

+-------+--------+-------+----------+
| Order | Agent  | Team  | Revenue  |
+-------+--------+-------+----------+
| 1     | Galpo  | Alpha |  1590.00 |
| 4     | Galpo  | Alpha |  2130.00 |
| 2     | Harry  | Alpha |  1745.00 |
| 6     | Harry  | Alpha |   815.00 |
| 8     | Harry  | Alpha |  1840.00 |
| 3     | Rosita | Bravo |  2410.00 |
| 7     | Rosita | Bravo |  1485.00 |
| 10    | Rosita | Bravo |  2455.00 |
| 5     | Erika  | Bravo |  2000.00 |
| 9     | Erika  | Bravo |   910.00 |
|       |        |       | 17380.00 |
+-------+--------+-------+----------+

任何建议表示赞赏。

标签： mysqlsql

Answer 1

请尝试with rollup：

SELECT . . .
FROM line_item JOIN
     order_
     ON line_item.order_id = order_.order_id JOIN
     sales_agent
     ON order_.agent_id = sales_agent.agent_id JOIN
     sales_team
     ON sales_agent.team_id  = sales_team.team_id JOIN
     product
     ON line_item.product_id = product.product_id)
GROUP BY order_.order_id WITH ROLLUP