这样的MySQL查询语句怎么写？

Question

有这样一个表，主键为 10001 的员工有17条年薪数据。现在我的需求是这样的，就是用 SQL 语句输出这样的格式：

emp_no	from_date	to_date	salary	差额
10001	1986-06-26	1987-06-26	60117	上一年的年薪减去今年年薪的差额
10001	1987-06-26	1988-06-25	62102	上一年的年薪减去今年年薪的差额
10001	1988-06-25	1989-06-25	66074	上一年的年薪减去今年年薪的差额
.........	.........	.........	.........	.........
........	.........	.........	.........	.........
.........	.........	.........	.........	.........

更进一步的需求，以 一行(记住是一行) 的格式显示，输出格式如下：

emp_no	from_date	to_date	salary	差额	from_date	to_date	salary	差额	from_date	to_date	salary	差额	...	...	...	...

这是数据库的地址，有心帮忙的朋友可以直接在数据库上进行测试。

host:125.42.176.217:63306
user:ousikongjian
password: hawk@#
database: employees
table: salaries

Answer 1

YEAR()用于获取date的年。 SELECT a.*, (b.salary-a.salary) AS '差额' FROM salaries a, salaries b WHERE a.emp_no=10001 AND b.emp_no=10001 AND YEAR(a.from_date)=YEAR(b.from_date)-1;

Answer 2

类似于这样的：CREATE TABLE tt_test (id INT DEFAULT 0, CODE INT DEFAULT 0, from_tm DATETIME, to_tm DATETIME, salary INT); SELECT tt.code, '2005-03-07' AS from_tm, '2006-03-07' AS to_tm, MAX(CASE WHEN tt.from_tm = '2005-03-07' THEN tt.salary END) AS salary, MAX(CASE WHEN tt.from_tm = '2005-03-07' THEN tt.s END) AS '差额', '2006-03-07' AS from_tm, '2007-03-07' AS to_tm, MAX(CASE WHEN tt.from_tm = '2006-03-07' THEN tt.salary END) AS salary, MAX(CASE WHEN tt.from_tm = '2006-03-07' THEN tt.s END) AS '差额', '2007-03-07' AS from_tm, '2008-03-07' AS to_tm, MAX(CASE WHEN tt.from_tm = '2007-03-07' THEN tt.salary END) AS salary, MAX(CASE WHEN tt.from_tm = '2007-03-07' THEN tt.s END) AS '差额', '2008-03-07' AS from_tm, '2009-03-07' AS to_tm, MAX(CASE WHEN tt.from_tm = '2008-03-07' THEN tt.salary END) AS salary, MAX(CASE WHEN tt.from_tm = '2008-03-07' THEN tt.s END) AS '差额', '2009-03-07' AS from_tm, '2010-03-07' AS to_tm, MAX(CASE WHEN tt.from_tm = '2009-03-07' THEN tt.salary END) AS salary, MAX(CASE WHEN tt.from_tm = '2009-03-07' THEN tt.s END) AS '差额', '2010-03-07' AS from_tm, '2011-03-07' AS to_tm, MAX(CASE WHEN tt.from_tm = '2010-03-07' THEN tt.salary END) AS salary, MAX(CASE WHEN tt.from_tm = '2010-03-07' THEN tt.s END) AS '差额' FROM (SELECT t.code, DATE_FORMAT(t.from_tm, '%Y-%m-%d') AS from_tm, DATE_FORMAT(t.to_tm, '%Y-%m-%d') AS to_tm, t.salary, CASE WHEN @s IS NULL OR @c != t.code THEN t.salary ELSE t.salary - @s END AS s, CASE WHEN @c != t.code THEN @s := 0 ELSE @s END AS cx, @s := t.salary, @c := t.code FROM (SELECT @s := 0) r, (SELECT @c := '') c, tt_test t ORDER BY t.code, t.from_tm ) tt GROUP BY tt.code ; INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('2','0','2018-03-07 10:14:08','2017-03-07 10:14:20','1000'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('3','0','2017-03-07 10:14:08','2016-03-07 10:14:20','1005'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('4','0','2016-03-07 10:14:08','2015-03-07 10:14:20','1100'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('5','0','2015-03-07 10:14:08','2014-03-07 10:14:20','905'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('6','0','2014-03-07 10:14:08','2013-03-07 10:14:20','987'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('7','0','2013-03-07 10:14:08','2012-03-07 10:14:20','950'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('8','0','2012-03-07 10:14:08','2011-03-07 10:14:20','997'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('9','0','2011-03-07 10:14:08','2010-03-07 10:14:20','900'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('10','0','2009-03-07 10:14:08','2008-03-07 10:14:20','890'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('11','0','2008-03-07 10:14:08','2007-03-07 10:14:20','800'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('12','0','2007-03-07 10:14:08','2006-03-07 10:14:20','850'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('13','0','2006-03-07 10:14:08','2005-03-07 10:14:20','843'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('14','1','2018-03-07 10:14:08','2017-03-07 10:14:20','1255'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('15','1','2017-03-07 10:14:08','2016-03-07 10:14:20','1224'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('16','1','2016-03-07 10:14:08','2015-03-07 10:14:20','1324'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('17','1','2015-03-07 10:14:08','2014-03-07 10:14:20','1122'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('18','1','2014-03-07 10:14:08','2013-03-07 10:14:20','1025'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('19','1','2013-03-07 10:14:08','2012-03-07 10:14:20','997'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('20','1','2012-03-07 10:14:08','2011-03-07 10:14:20','897'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('21','1','2011-03-07 10:14:08','2010-03-07 10:14:20','927'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('22','1','2010-03-07 10:14:08','2009-03-07 10:14:20','995'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('23','1','2009-03-07 10:14:08','2008-03-07 10:14:20','901'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('24','1','2008-03-07 10:14:08','2007-03-07 10:14:20','923'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('25','1','2007-03-07 10:14:08','2006-03-07 10:14:20','899'); INSERT INTO tt_test (id, code, to_tm, from_tm, salary) VALUES('26','1','2006-03-07 10:14:08','2005-03-07 10:14:20','887');

Answer 3

你想问的是SQL怎么写，还是ORM怎么写？