首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >带计数的MySql透视表

问带计数的MySql透视表
EN

Stack Overflow用户
提问于 2022-04-13 03:59:30
回答 1查看 187关注 0票数 1

我的下表是客户订单。

代码语言:javascript
复制
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

代码语言:javascript
复制
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份。

代码语言:javascript
复制
               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  

我在网上看了很多遍,但在这项任务上失败得很惨。如能提供任何协助,将不胜感激。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-04-13 04:06:36

我们可以进行两步聚合。首先,按客户和月进行汇总,以获得每个月每个客户的各种计数。然后按月和支点进行汇总,以得到最终结果。

代码语言:javascript
复制
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;
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/71851703

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档