mysql - 如何按发票日期从上个月 26 日到当月 25 日对数据进行分组？

Question

发票流程对上个月 26 日至当月 25 日期间提供的服务进行分组。所以我想按 client_id 对他们每月需要支付的费用进行分组。

+-----------+-----------+------------+-------+--------------+
| person_id | client_id | service_id | price | service_date |
+-----------+-----------+------------+-------+--------------+
| 101       | 1001      | 301        | 1000  | 2019-11-20   |
+-----------+-----------+------------+-------+--------------+
| 106       | 1001      | 301        | 1000  | 2019-11-24   |
+-----------+-----------+------------+-------+--------------+
| 102       | 1002      | 301        | 1000  | 2019-11-25   |
+-----------+-----------+------------+-------+--------------+
| 105       | 1001      | 301        | 1000  | 2019-11-26   |
+-----------+-----------+------------+-------+--------------+
| 103       | 1002      | 301        | 1000  | 2019-12-02   |
+-----------+-----------+------------+-------+--------------+
| 111       | 1002      | 301        | 1000  | 2019-12-05   |
+-----------+-----------+------------+-------+--------------+

根据上述数据，我期望得到以下结果:

+-----------+-----------+------------+
| client_id | total     | month      |
+-----------+-----------+------------+
| 1001      | 2000      | november   |
+-----------+-----------+------------+
| 1002      | 1000      | november   |
+-----------+-----------+------------+
| 1001      | 1000      | december   |
+-----------+-----------+------------+
| 1002      | 2000      | december   |
+-----------+-----------+------------+

编辑:service_date 是 %d/%m/%Y 格式的日期

EDIT2:我将 service_date 的日期格式更改为 %Y-%m-%d

数据库 fiddle :https://www.db-fiddle.com/f/v1Xty9c1SAp2PfaWCC4WvK/0

Answer 1

要获得所需的结果，您需要按调整后的月份(和年份)进行分组。计算这些的最简单方法是利用 MySQL 在数字上下文中将 bool 值视为 1 或 0 的事实，因此我们可以使用:

(MONTH(service_date) + (DAY(service_date) >= 26) - 1) % 12 + 1 AS month_num
YEAR(service_date) + (MONTH(service_date) = 12 AND DAY(service_date) >= 26) AS year

由于我们必须使用这些值来生成月份名称(但我们需要数字值进行排序)，因此最简单的方法是在子查询(或 CTE，如果您使用 MySQL 8+)中计算它们:

SELECT client_id,
       total, 
       year, 
       MONTHNAME(CONCAT_WS('-', year, month_num, '01')) AS month
FROM (SELECT client_id, 
             SUM(price) AS total,
             (MONTH(service_date) + (DAY(service_date) >= 26) - 1) % 12 + 1 AS month_num,
             YEAR(service_date) + (MONTH(service_date) = 12 AND DAY(service_date) >= 26) AS year
      FROM mytable
      GROUP BY client_id, month_num, year
) t
ORDER BY client_id, year, month_num

输出(用于我的扩展演示)

client_id   total   year    month
1001        2000    2019    November
1001        1000    2019    December
1001        2000    2020    January
1002        1000    2019    November
1002        2000    2019    December
1002        1000    2020    January

Demo on dbfiddle