第七色在线视频,2021少妇久久久久久久久久,亚洲欧洲精品成人久久av18,亚洲国产精品特色大片观看完整版,孙宇晨将参加特朗普的晚宴

為了賬號安全，請及時綁定郵箱和手機立即綁定
已解決430363個問題，去搜搜看，總會有你想問的

這樣的MySQL查詢語句怎么寫？

這樣的MySQL查詢語句怎么寫？

胡說叔叔 2019-03-15 14:15:18
有這樣一個表，主鍵為 10001 的員工有17條年薪數(shù)據(jù)?，F(xiàn)在我的需求是這樣的，就是用 SQL 語句輸出這樣的格式：emp_nofrom_dateto_datesalary差額100011986-06-261987-06-2660117上一年的年薪減去今年年薪的差額100011987-06-261988-06-2562102上一年的年薪減去今年年薪的差額100011988-06-251989-06-2566074上一年的年薪減去今年年薪的差額......................................................................................................................................更進一步的需求，以 一行(記住是一行) 的格式顯示，輸出格式如下：emp_nofrom_dateto_datesalary差額from_dateto_datesalary差額from_dateto_datesalary差額............這是數(shù)據(jù)庫的地址，有心幫忙的朋友可以直接在數(shù)據(jù)庫上進行測試。host:125.42.176.217:63306user:ousikongjianpassword: hawk@#database: employeestable: salaries
查看完整描述

4 回答

?
Helenr

TA貢獻1780條經(jīng)驗 獲得超4個贊

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;

https://img1.sycdn.imooc.com//5c90a76600013d2b03920317.jpg

查看完整回答
反對 回復(fù) 2019-03-19
?
臨摹微笑

TA貢獻1982條經(jīng)驗 獲得超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');


查看完整回答
反對 回復(fù) 2019-03-19
  • 4 回答
  • 0 關(guān)注
  • 529 瀏覽
慕課專欄
更多

添加回答

舉報

0/150
提交
取消
微信客服

購課補貼
聯(lián)系客服咨詢優(yōu)惠詳情

 幫助反饋 APP下載

慕課網(wǎng)APP
您的移動學(xué)習(xí)伙伴

 公眾號

掃描二維碼
關(guān)注慕課網(wǎng)微信公眾號