【MySQL】日期格式为 YYYY-MM 无法直接使用 DATE_SUB 函数的解决方案（特殊处理或 PERIOD_DIFF 函数）

本文链接：https://blog.csdn.net/weixin_50223520/article/details/135551022

力扣题

1、题目地址

2、模拟表

表：Accounts

Column Name	Type
account_id	int
max_income	int

account_id 是这张表具有唯一值的列。
每行包含一个银行账户每月最大收入的信息。

表：Transactions

Column Name	Type
transaction_id	int
account_id	int
type	ENUM
amount	int
day	datetime

transaction_id 是这张表具有唯一值的列。
每行包含一条转账信息。
type 是枚举类型（包含’Creditor’,‘Debtor’），其中 ‘Creditor’ 表示用户向其账户存入资金，‘Debtor’ 表示用户从其账户取出资金。
amount 是交易过程中的存入/取出的金额。

3、要求

如果一个账户在连续两个及以上月份的总收入超过最大收入（max_income），那么认为这个账户可疑。
账户当月总收入是当月存入资金总数（即 transactions 表中 type 字段的 ‘Creditor’）。
编写一个解决方案，报告所有的可疑账户。
以任意顺序返回结果表

4、示例

输入：

Accounts 表：

account_id	max_income
3	21000
4	10400

Transactions 表：

transaction_id	account_id	type	amount	day
2	3	Creditor	107100	2021-06-02 11:38:14
4	4	Creditor	10400	2021-06-20 12:39:18
11	4	Debtor	58800	2021-07-23 12:41:55
1	4	Creditor	49300	2021-05-03 16:11:04
15	3	Debtor	75500	2021-05-23 14:40:20
10	3	Creditor	102100	2021-06-15 10:37:16
14	4	Creditor	56300	2021-07-21 12:12:25
19	4	Debtor	101100	2021-05-09 15:21:49
8	3	Creditor	64900	2021-07-26 15:09:56
7	3	Creditor	90900	2021-06-14 11:23:07

输出：

account_id
3

解释：

对于账户 3：

在 2021年6月，用户收入为 107100 + 102100 + 90900 = 300100。
在 2021年7月，用户收入为 64900。

可见收入连续两月超过21000的最大收入，因此账户3列入结果表中。

对于账户 4：

在 2021年5月，用户收入为 49300。
在 2021年6月，用户收入为 10400。
在 2021年7月，用户收入为 56300。

可见收入在5月与7月超过了最大收入，但6月没有。因为账户没有没有连续两月超过最大收入，账户4不列入结果表中。

5、代码编写

思路

1、Transactions 中根据 account_id 和 day里面的年月进行分组，加上查询条件 type=‘Creditor’（存入的钱），就可以查询到不同账户在不同月份存钱的总量

SELECT account_id, DATE_FORMAT(day, '%Y-%m') AS `month`, SUM(amount) AS sAmount
FROM Transactions
WHERE `type` = 'Creditor'
GROUP BY account_id, `month`

| account_id | month   | sAmount |
| ---------- | ------- | ------- |
| 3          | 2021-06 | 300100  |
| 4          | 2021-06 | 10400   |
| 4          | 2021-05 | 49300   |
| 4          | 2021-07 | 56300   |
| 3          | 2021-07 | 64900   |

2、要求里面是要连续两个月或以上总收入超过最大收入，那我们按最少两个月就行，将上面查询出来的连接自己，account_id 相同，月份（month）差距是一个月，满足总收入（sAmount）超过最大收入（max_income）就行

第一版写法（日期 YYYY-MM 格式特殊处理）

WITH tmp AS(
    SELECT account_id, DATE_FORMAT(day, '%Y-%m') AS `month`, SUM(amount) AS sAmount
    FROM Transactions
    WHERE `type` = 'Creditor'
    GROUP BY account_id, `month`
)
SELECT DISTINCT one.account_id
FROM tmp AS one, tmp AS two
WHERE one.account_id = two.account_id
AND CONCAT(one.month, '01') = DATE_SUB(CONCAT(two.month, '-01'), INTERVAL 1 MONTH)
AND one.sAmount > (SELECT max_income FROM Accounts WHERE account_id = one.account_id)
AND two.sAmount > (SELECT max_income FROM Accounts WHERE account_id = two.account_id)

说一下我为什么这样用：
首先我测试 select date_sub('2023-02', interval 1 month)，期望结果是 2023-01，但是结果是 Null，
其次我再测试 select date_sub('2023-02-01', interval 1 month)，这次正常输出，结果是 2023-01-01
所以就有了个想法，拼接后面的 -01，使用 concat 函数进行拼接即可，即 select date_sub(concat('2023-02','-01'), interval 1 month)

第二版写法（日期格式用 YYYYMM 使用 PERIOD_DIFF 函数）

PERIOD_DIFF
1、PERIOD_DIFF() 函数返回两日期之间的差异，结果以月份计算。
2、period1 和 period2 应采用相同的格式（格式为YYMM或YYYYMM）
3、语法：PERIOD_DIFF(period1, period2)

测试：
select period_diff('202302', '202301')，结果 1
select period_diff('2302', '2301')，结果 1
select period_diff('202301', '202302')，结果 -1
select period_diff('2301', '2302')，结果 -1

参考：MySQL PERIOD_DIFF() 函数

WITH tmp AS(
    SELECT account_id, DATE_FORMAT(day, '%Y%m') AS `month`, SUM(amount) AS sAmount
    FROM Transactions
    WHERE `type` = 'Creditor'
    GROUP BY account_id, `month`
)
SELECT DISTINCT one.account_id
FROM tmp AS one, tmp AS two
WHERE one.account_id = two.account_id
AND PERIOD_DIFF(two.month, one.month) = 1
AND one.sAmount > (SELECT max_income FROM Accounts WHERE account_id = one.account_id)
AND two.sAmount > (SELECT max_income FROM Accounts WHERE account_id = two.account_id)