能否不使用子查询按日期分组统计delivery与pickup订单金额？

你要的需求可以通过条件聚合直接完成，不用子查询。核心思路是在聚合函数SUM()里嵌套CASE WHEN，按订单类型筛选后分别求和，再按日期分组。

原订单表数据 #

Table: orders

+-------------+----------+-------------------------------+
| date        | price    | type (delivery or pickup)     |
+-------------+----------+-------------------------------+
| 2022-01-12  |     113  | delivery                      |
| 2022-01-12  |     23   | pickup                        |
| 2022-04-12  |     45   | pickup                        |
| 2022-05-17  |     17   | delivery                      |
| 2022-05-17  |     58   | delivery                      |
| 2021-08-13  |     13   | pickup                        |
| 2021-08-24  |     24   | pickup                        |
+-------------+----------+-------------------------------+

实现SQL #

SELECT 
    date,
    SUM(CASE WHEN type = 'delivery' THEN price ELSE 0 END) AS `amount of delivery`,
    SUM(CASE WHEN type = 'pickup' THEN price ELSE 0 END) AS `amount of pickup`
FROM orders
GROUP BY date
ORDER BY date;

执行结果 #

+-------------+--------------------+-------------------+
| date        | amount of delivery | amount of pickup  |
+-------------+--------------------+-------------------+
| 2021-08-13  |                  0 |                13 |
| 2021-08-24  |                  0 |                24 |
| 2022-01-12  |                113 |                23 |
| 2022-04-12  |                  0 |                45 |
| 2022-05-17  |                 75 |                 0 |
+-------------+--------------------+-------------------+

说明 #

• CASE WHEN会根据订单类型判断，只累加对应类型的金额，否则加0，避免出现NULL值
• GROUP BY date确保按日期分组统计
• 整个查询没有用到任何子查询，完全符合你的要求

内容的提问来源于stack exchange，提问作者Pavel Savel'ichev