订单表。
id | created_date | price |
---|---|---|
1 | 2021-09-01 | 100 |
2 | 2021-09-01 | 50 |
3 | 2020-09-01 | 50 |
4 | 2020-09-01 | 150 |
5 | 2019-09-01 | 100 |
订购详细信息表: -
id | order_id | product_quantity |
---|---|---|
1 | 1 | 10 |
2 | 2 | 20 |
3 | 1 | 30 |
4 | 3 | 10 |
5 | 3 | 20 |
6 | 4 | 30 |
7 | 5 | 20 |
所以我需要一个MySQL查询,以获得关于每年订单数量和price和product数量的统计数据。
预期的表格
year(created_date) | orders_count | product_quantity_in_orders | price |
---|---|---|---|
2021 | 2 | 60 | 150 |
2020 | 2 | 60 | 150 |
2019 | 1 | 20 | 100 |
分析解答
请尝试一下。
SELECT YEAR(o.created_date) year_created
, COUNT(o.id) orders_count
, SUM(t.product_quantity) product_quantity_in_orders
, SUM(o.price) price
FROM orders o
INNER JOIN (SELECT order_id
, SUM(product_quantity) product_quantity
FROM order_details
GROUP BY order_id) t
ON o.id = t.order_id
GROUP BY YEAR(o.created_date)