SQL实战练习题(8)-求相邻行差值-leetcode sql 197

目录

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

问题描述

数据SQL

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

INSERT INTO salary (amount) VALUES
	 (9000),
	 (6000),
	 (10000),
	 (7000),
	 (6000),
	 (8000),
	 (10000),
	 (7000),
	 (6000),
	 (8000),
	 (10000),
	 (7000),
	 (6000),
	 (8000);

答案

with
tbl_01 as (
	select 
		s.id,    -- 充当日期
		s.amount
	from salary s 
	order by s.id asc -- 升序
)
,tbl_02 as (
	select t2.id, t2.amount, 
		case t1.id
			when 1 then 0 
			else (t2.amount - t1.amount) 
		end as diff
	from tbl_01 t1, tbl_01 t2
	where t1.id = t2.id - 1
)
select * from tbl_02;