SQL实战练习题(7)-连续3个月工资的求和-leetcode sql 579

目录

• 题目描述
• 数据SQL
• 答案

题目描述

数据SQL

CREATE TABLE `salary` (
  `id` int NOT NULL AUTO_INCREMENT,
  `employee_id` int DEFAULT NULL,
  `amount` int DEFAULT NULL,
  `pay_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO leetcode_db.salary (employee_id,amount,pay_date) VALUES
	 (1,9000,'2017-03-31'),
	 (2,6000,'2017-03-31'),
	 (3,10000,'2017-03-31'),
	 (1,7000,'2017-02-28'),
	 (2,6000,'2017-02-28'),
	 (3,8000,'2017-02-28'),
	 (3,10000,'2017-04-30'),
	 (1,7000,'2017-04-30'),
	 (2,6000,'2017-04-30'),
	 (3,8000,'2017-05-28'),
	 (3,10000,'2017-07-28'),
	 (1,7000,'2017-06-28'),
	 (2,6000,'2017-06-28'),
	 (3,8000,'2017-06-28');

答案

with
tbl_01 as (
	SELECT 
		s1.employee_id,
		(DATE_FORMAT(s1.pay_date, '%m')+0) as mn,   -- 日期格式化成字符串，转int
		s1.amount
	FROM salary s1
	order by s1.employee_id ASC 
)
,tbl_02 as (
	SELECT 
		s2.employee_id,
		s2.mn,
		SUM(s3.amount) as amount_of_recent_3mn
	FROM tbl_01 s2, tbl_01 s3
	WHERE s2.employee_id = s3.employee_id and s2.mn-2 <= s3.mn and s3.mn <= s2.mn -- s3.mn BETWEEN s2.mn-2 and s2.mn  注意：不能连续使用2个大于等于的逻辑(因为返回结果是true/false)（s2.mn-2 <= s3.mn <= s2.mn)
	GROUP BY s3.employee_id, s2.mn
)
SELECT
	tbl_01.*,
	tbl_02.amount_of_recent_3mn
FROM
	tbl_01
join tbl_02 on
	tbl_01.employee_id = tbl_02.employee_id
	and tbl_01.mn = tbl_02.mn
order by
	tbl_01.employee_id ASC,
	tbl_01.mn DESC ;

select 1 < 2 < 2 返回结果是1 (mysql中true、false分别用1、0表示)