时间戳

首页 > 解决方案 > 按类型和日期计算值和分组

mysql - 按类型和日期计算值和分组

问题描述

我有这张表（日期格式：dd/mm/yyyy）

+ order_id + buyer +    date    +    status   +
+----------+-------+------------+-------------+
+  CSR001  +  AAA  + 01/01/2020 +  delivered  +
+  CSR002  +  AAA  + 03/01/2020 +   canceled  +
+  CSR003  +  BBB  + 01/01/2020 +  delivered  +
+  CSR004  +  BBB  + 04/01/2020 +  delivered  +
+  CSR005  +  AAA  + 01/02/2020 +   canceled  +
+  CSR006  +  BBB  + 01/02/2020 +  delivered  +   
+  CSR007  +  AAA  + 01/02/2020 +  delivered  +
+  CSR008  +  AAA  + 01/02/2020 +  delivered  +
+----------+-------+------------+-------------+

在 mysql 中创建视图并使其成为这样的查询是什么，（按买家、状态分组，并计算每月交付/取消的价值数量）

 + buyer   +  status   +  january  +  february  +
+----------+-----------+-----------+------------+
+  AAA     + delivered +     1     +      2     +
+  AAA     + cancelled +     1     +      1     +
+  BBB     + delivered +     2     +      0     +
+  BBB     + cancelled +     0     +      0     +
+----------+-----------+-----------+------------+

标签： mysql

解决方案

您可以在内部设置条件count来区分月份：

SELECT t.buyer,
       t.status,
       COUNT(IF(MONTH(t.date) = 1, 1, NULL)) AS jan,
       COUNT(IF(MONTH(t.date) = 2, 1, NULL)) AS feb
FROM TABLE AS t
GROUP BY buyer,
         status;

推荐阅读