我的下表是客户订单。
order_id | customer_id | fulfilled | order_date
---------------------------------------------------
123 | 1234 | 1 | 2022/01/12
126 | 1235 | 1 | 2022/01/18
127 | 1235 | 0 | 2022/01/19
128 | 1236 | 1 | 2022/01/01
129 | 1236 | 0 | 2022/01/03
130 | 1236 | 1 | 2022/01/04
131 | 1237 | 1 | 2022/01/01
132 | 1237 | 1 | 2022/01/03
133 | 1237 | 1 | 2022/01/04
134 | 1238 | 1 | 2022/02/12
135 | 1239 | 1 | 2022/02/18
136 | 1239 | 0 | 2022/02/19
137 | 1239 | 1 | 2022/02/20
138 | 1239 | 1 | 2022/02/21
139 | 1240 | 1 | 2022/02/01
140 | 1240 | 1 | 2022/02/03
141 | 1240 | 1 | 2022/02/04
142 | 1241 | 1 | 2022/02/01
143 | 1241 | 0 | 2022/02/03
144 | 1241 | 1 | 2022/02/04
145 | 1241 | 1 | 2022/02/04
146 | 1241 | 1 | 2022/02/04下面的SQL
CREATE TABLE `orders` (
`order_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`fulfilled` tinyint(1) NOT NULL DEFAULT 0,
`order_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `orders` (`order_id`, `customer_id`, `fulfilled`, `order_date`) VALUES
(123, 1234, 1, '2022-01-12'),
(126, 1235, 1, '2022-01-18'),
(127, 1235, 0, '2022-01-19'),
(128, 1236, 1, '2022-01-01'),
(129, 1236, 0, '2022-01-03'),
(130, 1236, 1, '2022-01-04'),
(131, 1237, 1, '2022-01-01'),
(132, 1237, 1, '2022-01-03'),
(133, 1237, 1, '2022-01-04'),
(134, 1238, 1, '2022-02-12'),
(135, 1239, 1, '2022-02-18'),
(136, 1239, 0, '2022-02-19'),
(137, 1239, 1, '2022-02-20'),
(138, 1239, 1, '2022-02-21'),
(139, 1240, 1, '2022-02-01'),
(140, 1240, 1, '2022-02-03'),
(141, 1240, 1, '2022-02-04'),
(142, 1241, 1, '2022-02-01'),
(143, 1241, 0, '2022-02-03'),
(144, 1241, 1, '2022-02-04'),
(145, 1241, 1, '2022-02-04'),
(146, 1241, 1, '2022-02-04');
ALTER TABLE `orders` ADD PRIMARY KEY (`order_id`);我想要创建一个MySQL查询,将这些数据排列到一个支点表中,以显示每个月有多少客户拥有1、2、3或4履行订单(完全填充列= 1)。每个客户的订单永远不会超过4份。
Customers Customers Customers Customers
Month/Year | with 1 Order | with 2 Orders | with 3 Orders | with 4 Orders
-------------------------------------------------------------------------------------------
Jan 22 | 2 | 2 | 1 | 0
Feb 22 | 1 | 0 | 2 | 1 我在网上看了很多遍,但在这项任务上失败得很惨。如能提供任何协助,将不胜感激。
发布于 2022-04-13 04:06:36
我们可以进行两步聚合。首先,按客户和月进行汇总,以获得每个月每个客户的各种计数。然后按月和支点进行汇总,以得到最终结果。
SELECT month_year,
SUM(total = 1) AS one_order,
SUM(total = 2) AS two_order,
SUM(total = 3) AS three_order,
SUM(total = 4) AS four_order,
FROM
(
SELECT DATE_FORMAT(order_date, '%b %y') AS month_year, customer_id,
SUM(fulfilled) AS total
FROM orders
GROUP BY 1, 2
) t
GROUP BY month_year;https://stackoverflow.com/questions/71851703
复制相似问题