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

图片描述

有这样一个表,主键为 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
阅读 3.4k
5 个回答

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;

图片描述

类似于这样的:
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');

你想问的是SQL怎么写,还是ORM怎么写?

使用 left join 到上一年数据条目 再算出差额

伪sql:
select a.*, (b.salary - a.salary) as 差额 from t1 as a
left join t1 as b
on a.emp_no = b.emp_no and b.from_data = (a.from_data - 1年时间) and b.to_data = (a.to_data - 1年时间)

select a.*,a.salary-b.salary as diff from salaries a left join salaries b on a.from_date=b.to_date where a.emp_no=10001 and b.emp_no=10001 order by from_date;


**行转列很容易,自己百度下就行,但我觉得意义不大,你大可以在页面展现的时候实现,而不是纠结于sql语句。**
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题