我想从 mysql 中的同一个表中获取多个计数

debugcn 发表于 Dev

字节码

我有一个包含用户订单详细信息的表格，我想要订单总数，以及上个月的订单数量和上周的订单数量。

表结构

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| id             | bigint(21 ) | NO   |     | NULL    |       |
| customer_id    | bigint(21)  | YES  |     | NULL    |       |
| provider_id    | varchar(20) | YES  |     | NULL    |       |
| order_datetime | date        | YES  |     | NULL    |       |
| pickup_datetime| date        | YES  |     | NULL    |       |
| status         | tinyint(4)  | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

表数据

[{"id": 1,"customer_id": 1,"provider_id": 2,"order_datetime": "2016-11-24 14:52:51","pickup_datetime": "2016-11-24 14:53:19","status": 3}, {"id": 2,"customer_id": 1,"provider_id": 2,"order_datetime": "2016-11-24 15:18:30","pickup_datetime": "2016-11-24 15:18:36","status": 3}, {"id": 3,"customer_id": 1,"provider_id": 2,"order_datetime": "2016-11-24 17:19:27","pickup_datetime": "2016-11-24 17:19:33","status": 3}, {"id": 4,"customer_id": 0,"provider_id": 2,"order_datetime": "2016-11-24 17:30:46","pickup_datetime": "2016-11-24 17:30:51","status": 3}, {"id": 5,"customer_id": 1,"provider_id": 2,"order_datetime": "2016-11-24 17:37:52","pickup_datetime": "2016-11-24 17:38:00","status": 3}, {"id": 6,"customer_id": 1,"provider_id": 2,"order_datetime": "2016-11-25 11:31:51","pickup_datetime": "2016-11-25 11:32:14","status": 3}, {"id": 7,"customer_id": 1,"provider_id": 2,"order_datetime": "2016-11-25 11:36:05","pickup_datetime": "2016-11-25 11:36:16","status": 3}, {"id": 8,"customer_id": 1,"provider_id": 2,"order_datetime": "2016-11-25 11:41:10","pickup_datetime": "2016-11-25 11:41:17","status": 3}]

提前致谢

蒂姆·比格莱森

您可以在此处使用条件聚合通过单次传递订单表来完成此操作：

SELECT
    SUM(CASE WHEN order_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK) AND NOW()
             THEN 1 ELSE 0 END) AS last_week_cnt,
    SUM(CASE WHEN order_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()
             THEN 1 ELSE 0 END) AS last_month_cnt,
    COUNT(*) AS all_cnt
FROM orders

这应该优于 UNION 方法。请注意，如果表中的给定记录可能对应于多个订单，并且有一列用于该列，那么我们可以稍微修改我的查询以对该列求和，而不是默认为每条记录一个订单。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。