【題目】
“雇員表“中記錄了員工的資訊,“薪水表“中記錄了對應員工發放的薪水。兩表通過“雇員編号”關聯。
查找目前所有雇員入職以來的薪水漲幅,給出雇員編号以及其對應的薪水漲幅,并按照薪水漲幅進行升序。
(注:薪水表中結束日期為2004-01-01的才是目前員工,否則是已離職員工)
【解題思路】
要求出目前所有雇員入職以來的薪水漲幅,薪水漲幅=目前薪水-入職薪水。
是以,需要知道雇員的入職薪水和目前薪水。
目前薪水是“薪水表“中的“結束日期”=2004-01-01,這一行對應的薪水。
入職薪水是“雇員表“中的“雇用日期”=“薪水表“中的“起始日期”,這一行對應的薪水。
【解題步驟】
- 目前薪水
目前薪水是“薪水表“中的“結束日期”=2004-01-01。從“薪水表“給的案例資料可以看出,“雇員編号”(10002)有兩條薪水記錄,說明他經曆過一次漲薪。“雇員編号”(10005)的薪水“結束日期”不是2004-01-01,說明該員工已經離職。“雇員編号”(10006)有一條薪水記錄,說明他沒有經曆過漲薪。
select 雇員編号,薪水 as 目前薪水
from 薪水表
where 結束日期 = '2004-01-01';
- 入職薪水
入職薪水是“雇員表“中的“雇用日期”=“薪水表“中的“起始日期”。
這涉及到兩個表,是以需要用到多表聯結,拿出《猴子 從零學會SQL》裡面的多表聯結圖。
使用哪種聯結呢?
因為題目要求的是“查找目前所有雇員”,是以用“雇員表”為左表,保留左表的全部資料,是以使用左聯結。
select a.雇員編号,薪水 as 入職薪水
from 雇員表 as a
left join 薪水表 as b
on a.雇員編号 = b.雇員編号
where a.雇用日期 = b.起始日期;
因為雇員表中還包含了離職員工,而題目要求的是“目前所有雇員“,也就是在職員工,是以需要用where子句篩出在職的員工。也就是結束日期 = '2004-01-01'的員工編号:
where a.雇員編号 in
(select 雇員編号
where 結束日期 = '2004-01-01');
将兩表聯結和where條件加入,完整的sql就是:
where a.雇用日期 = b.起始日期 and a.雇員編号 in
3.薪水漲幅
把步驟1的查找結果當做臨時表m,把步驟2的查詢結果當做臨時表n。兩表通過“雇員編号”進行多表聯結。
因為要保留左表(m)的全部資料(在職的全部雇員),是以使用左聯結。
select m.雇員編号,目前薪水-入職薪水 as 薪水漲幅
from m
left join n
on m.雇員編号 = n.雇員編号;
4.按薪水漲幅進行升序
使用order by子句對查詢結果排序。把表m和表n的sql代碼代入,得到:
from
(select 雇員編号,薪水 as 目前薪水
from 薪水表
where 結束日期 = '2004-01-01') as m
left join
(select a.雇員編号,薪水 as 入職薪水
left join 薪水表 as b
where 結束日期 = '2004-01-01')) as n
on m.雇員編号 = n.雇員編号
order by 薪水漲幅;
【本題考點】
1.考查了解業務的能力。知道如何将“薪水漲幅“名額定義為入職薪水-目前薪水。
2.考查多表聯結。需要知道什麼情況下使用哪種聯結。
【舉一反三】
查找所有學生開學以來的成績漲幅,給出學生編号以及其對應的成績漲幅,并按照成績漲幅進行升序。
select m.學生編号,目前成績-入學成績 as 成績漲幅
(select 學生編号,成績 as 目前成績
from 成績表
where 結束日期 = '2011-10-02') as m
left join
(select a.學生編号,b.成績 as 入學成績
from 學生表 as a
left join 成績表 as b
on a.學生編号 = b.學生編号
where a.入學日期 = b.起始日期) as n
on m.學生編号 = n.學生編号
order by 成績漲幅;
推薦:如何從零學會sql?