一 引言

工作中常會遇到 “查詢工資最高/第二高/最低/倒數(shù)第N的員工” “銷售量前10的商品” 此類排序取前N的問題袜啃。這篇文章以查詢薪資第二高/倒數(shù)第二高的員工為例，給出了三種解決方法譬淳。

二 創(chuàng)建表?

CREATE TABLE `salaries` (

`emp_no` int(11) NOT NULL,

`salary` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`from_date`));

INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26'); INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25'); INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25'); INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25'); INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25'); INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24'); INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24'); INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24'); INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24'); INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23'); INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23'); INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23'); INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23'); INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22'); INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22'); INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22'); INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01'); INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03'); INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03'); INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03'); INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02'); INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02'); INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01'); INSERT INTO salaries VALUES(10003,40006,'1995-12-03','1996-12-02'); INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02'); INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02'); INSERT INTO salaries VALUES(10003,43636,'1998-12-02','1999-12-02'); INSERT INTO salaries VALUES(10003,43478,'1999-12-02','2000-12-01'); INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01'); INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01'); INSERT INTO salaries VALUES(10004,40054,'1986-12-01','1987-12-01'); INSERT INTO salaries VALUES(10004,42283,'1987-12-01','1988-11-30'); INSERT INTO salaries VALUES(10004,42542,'1988-11-30','1989-11-30'); INSERT INTO salaries VALUES(10004,46065,'1989-11-30','1990-11-30'); INSERT INTO salaries VALUES(10004,48271,'1990-11-30','1991-11-30'); INSERT INTO salaries VALUES(10004,50594,'1991-11-30','1992-11-29'); INSERT INTO salaries VALUES(10004,52119,'1992-11-29','1993-11-29'); INSERT INTO salaries VALUES(10004,54693,'1993-11-29','1994-11-29'); INSERT INTO salaries VALUES(10004,58326,'1994-11-29','1995-11-29'); INSERT INTO salaries VALUES(10004,60770,'1995-11-29','1996-11-28'); INSERT INTO salaries VALUES(10004,62566,'1996-11-28','1997-11-28'); INSERT INTO salaries VALUES(10004,64340,'1997-11-28','1998-11-28'); INSERT INTO salaries VALUES(10004,67096,'1998-11-28','1999-11-28'); INSERT INTO salaries VALUES(10004,69722,'1999-11-28','2000-11-27'); INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27'); INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01'); INSERT INTO salaries VALUES(10005,78228,'1989-09-12','1990-09-12'); INSERT INTO salaries VALUES(10005,82621,'1990-09-12','1991-09-12'); INSERT INTO salaries VALUES(10005,83735,'1991-09-12','1992-09-11'); INSERT INTO salaries VALUES(10005,85572,'1992-09-11','1993-09-11'); INSERT INTO salaries VALUES(10005,85076,'1993-09-11','1994-09-11'); INSERT INTO salaries VALUES(10005,86050,'1994-09-11','1995-09-11'); INSERT INTO salaries VALUES(10005,88448,'1995-09-11','1996-09-10'); INSERT INTO salaries VALUES(10005,88063,'1996-09-10','1997-09-10'); INSERT INTO salaries VALUES(10005,89724,'1997-09-10','1998-09-10'); INSERT INTO salaries VALUES(10005,90392,'1998-09-10','1999-09-10'); INSERT INTO salaries VALUES(10005,90531,'1999-09-10','2000-09-09'); INSERT INTO salaries VALUES(10005,91453,'2000-09-09','2001-09-09'); INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01'); INSERT INTO salaries VALUES(10006,43311,'1990-08-05','1991-08-05'); INSERT INTO salaries VALUES(10006,43311,'1991-08-05','1992-08-04'); INSERT INTO salaries VALUES(10006,43311,'1992-08-04','1993-08-04'); INSERT INTO salaries VALUES(10006,43311,'1993-08-04','1994-08-04'); INSERT INTO salaries VALUES(10006,43311,'1994-08-04','1995-08-04'); INSERT INTO salaries VALUES(10006,43311,'1995-08-04','1996-08-03'); INSERT INTO salaries VALUES(10006,43311,'1996-08-03','1997-08-03'); INSERT INTO salaries VALUES(10006,43311,'1997-08-03','1998-08-03'); INSERT INTO salaries VALUES(10006,43311,'1998-08-03','1999-08-03'); INSERT INTO salaries VALUES(10006,43311,'1999-08-03','2000-08-02'); INSERT INTO salaries VALUES(10006,43311,'2000-08-02','2001-08-02'); INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01'); INSERT INTO salaries VALUES(10007,56724,'1989-02-10','1990-02-10'); INSERT INTO salaries VALUES(10007,60740,'1990-02-10','1991-02-10'); INSERT INTO salaries VALUES(10007,62745,'1991-02-10','1992-02-10'); INSERT INTO salaries VALUES(10007,63475,'1992-02-10','1993-02-09'); INSERT INTO salaries VALUES(10007,63208,'1993-02-09','1994-02-09'); INSERT INTO salaries VALUES(10007,64563,'1994-02-09','1995-02-09'); INSERT INTO salaries VALUES(10007,68833,'1995-02-09','1996-02-09'); INSERT INTO salaries VALUES(10007,70220,'1996-02-09','1997-02-08'); INSERT INTO salaries VALUES(10007,73362,'1997-02-08','1998-02-08'); INSERT INTO salaries VALUES(10007,75582,'1998-02-08','1999-02-08'); INSERT INTO salaries VALUES(10007,79513,'1999-02-08','2000-02-08'); INSERT INTO salaries VALUES(10007,80083,'2000-02-08','2001-02-07'); INSERT INTO salaries VALUES(10007,84456,'2001-02-07','2002-02-07'); INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01'); INSERT INTO salaries VALUES(10008,46671,'1998-03-11','1999-03-11'); INSERT INTO salaries VALUES(10008,48584,'1999-03-11','2000-03-10'); INSERT INTO salaries VALUES(10008,52668,'2000-03-10','2000-07-31'); INSERT INTO salaries VALUES(10009,60929,'1985-02-18','1986-02-18'); INSERT INTO salaries VALUES(10009,64604,'1986-02-18','1987-02-18'); INSERT INTO salaries VALUES(10009,64780,'1987-02-18','1988-02-18'); INSERT INTO salaries VALUES(10009,66302,'1988-02-18','1989-02-17'); INSERT INTO salaries VALUES(10009,69042,'1989-02-17','1990-02-17'); INSERT INTO salaries VALUES(10009,70889,'1990-02-17','1991-02-17'); INSERT INTO salaries VALUES(10009,71434,'1991-02-17','1992-02-17'); INSERT INTO salaries VALUES(10009,74612,'1992-02-17','1993-02-16'); INSERT INTO salaries VALUES(10009,76518,'1993-02-16','1994-02-16'); INSERT INTO salaries VALUES(10009,78335,'1994-02-16','1995-02-16'); INSERT INTO salaries VALUES(10009,80944,'1995-02-16','1996-02-16'); INSERT INTO salaries VALUES(10009,82507,'1996-02-16','1997-02-15'); INSERT INTO salaries VALUES(10009,85875,'1997-02-15','1998-02-15'); INSERT INTO salaries VALUES(10009,89324,'1998-02-15','1999-02-15'); INSERT INTO salaries VALUES(10009,90668,'1999-02-15','2000-02-15'); INSERT INTO salaries VALUES(10009,93507,'2000-02-15','2001-02-14'); INSERT INTO salaries VALUES(10009,94443,'2001-02-14','2002-02-14');

三 查詢第三高薪資讳窟，如果沒有第三高勒极，用null表示

四 查詢to_date="9999-01-01"，薪資最高的員工

1. max函數(shù)只能取出一條

2. 改進(jìn)一下矛绘，把結(jié)果作為where子查詢

思路：查詢出最高工資耍休，作為子查詢，where salary=最高工資的員工

看來只有一個員工薪水等于94692货矮，沒有重復(fù)項(xiàng)

五? 查詢to_date="9999-01-01"羊精，薪資第二高的員工

先把所有的salary查出來看一下，第二高的是88958。

1. 法一： 第二名的特殊查法

這種方法只能用于取第二名喧锦。取其他的TopN不能用读规，如果有多個并列第二工資的員工只能查出一個

2. 法一修改，可以查出所有工資并列第二的員工

法一的基礎(chǔ)上嵌套一下燃少，salary作為查詢條件束亏，法一的代碼作為子查詢。

思路：查詢出第二工資作為子查詢阵具，where salary=第二工資的員工

3. 法二：order by + limit

這種方法實(shí)際上取出的是排列后第二行的記錄碍遍，只能取出一個記錄，無法查出所有并列第二的記錄

如果有兩個薪資并列第一阳液，查出來的可能是薪資第一的記錄怕敬。

這題第一名和第二名只有一個，可以用這種方法帘皿，但實(shí)際上并不嚴(yán)謹(jǐn)东跪。

4. 法二修改，可以查出所有工資并列第二的員工

思路：查詢出第二工資作為子查詢（salary加distinct)鹰溜，where salary=第二工資的員工

5 法三：排名法（通用方法）

原理：假如 a 排第10虽填，意味著前面有9個數(shù)比a大，有10個數(shù)大于等于a曹动，?求某個數(shù)的排名就是求前面有幾個數(shù)大于等于這個數(shù)

思路：重復(fù)用salaries表卤唉，命名為s1,s2。用where語句s1.salary<=s2.salary篩選出所有大于等于s1.salary的s2.salary仁期。然后統(tǒng)計每個s1.salary有多少個s2.salary大于等于它，就能得到每個s1.salary的排名

5.1 步驟一

重復(fù)用salaries表竭恬，命名為s1,s2跛蛋。用where語句s1.salary<=s2.salary篩選出所有大于等于s1.salary的s2.salary。

例如：當(dāng)salary=88958時痊硕，大于等于它的有88958赊级，84692 共2個，所以salary=88958排第2

5.2 步驟二

?count( distinct s2.salary) ：統(tǒng)計每個s1.salary有多少個s2.salary大于等于它得到每個s1.salary的排名

5.3 步驟三

那怎么取得第二名的數(shù)呢岔绸？ 只需要限制條件理逊，rank=2

不用group by （使用group by會導(dǎo)致s1.salary被去重）

六 查詢to_date="9999-01-01",薪資倒數(shù)第二的員工?

先把所有的salary查出來看一下，倒數(shù)第二高是72527盒揉。

1. 法一：order by + limit?

這種方法用在這里為什么出錯了呢晋被？ 因?yàn)?limit 1，1 取出來的是第二行的值刚盈。這題中羡洛，最高薪水有兩個，取出來的第二行是第二個最高薪水藕漱。

2. 法一改進(jìn)：給 salary 加 distinct 去重

去重后欲侮，第一名只有一行記錄崭闲，limit 1，1取到的是第二的薪水威蕉。但這種方法只能查詢salary刁俭，不能查出對應(yīng)的員工信息

3. 法一再改進(jìn)：salary作為查詢條件，嵌套子查詢

4. 法三： 排名法

上面說過韧涨，求某個數(shù)的排名就是求前面有幾個數(shù)大于等于這個數(shù)牍戚，假如 a 排第10，意味著前面有9個數(shù)比a大氓奈，有10個數(shù)大于等于a尿这。

那倒數(shù)也是一樣的杂靶，假如a排倒數(shù)第二，意味著比它小的數(shù)只有一個，有兩個數(shù)小于等于它（比它小的數(shù)和它本身）

求某個數(shù)的倒數(shù)排名义黎，就是求小于等于它的數(shù)有幾個

從結(jié)果可以看出，salary=72527時线定，只有72525本身和43311比它小早芭。說明salary=72525可能是倒數(shù)第二的薪資。接下來涧至，只需要統(tǒng)計 每個s1.salary 對應(yīng)的比它小的 s2.salay 有幾個腹躁，就能得到salary的倒數(shù)名次。

那么怎么用代碼查詢倒數(shù)第二呢南蓬，和前面查正數(shù)排名一樣纺非，只需要限制排名=2

不用group by （使用group by會導(dǎo)致s1.salary被去重）

六?查詢to_date="9999-01-01",薪資前三名

法一：order by+limit?

實(shí)際上取出的是排在前三行的工資，這題的數(shù)據(jù)集工資沒有重復(fù)赘方，可以這樣做烧颖，但如果工資有重復(fù)，這樣做就不嚴(yán)謹(jǐn)

法二：排名法

七 給員工薪資排名

八 其他

思路：可以看成兩個表窄陡，一個是id,name,salary.? managerid 另一個是managerid,name,salary炕淮。

?通過 managerid關(guān)聯(lián)兩個表。變成 id,name,salary,managerid,managerid(1),salary.這樣就把員工salary和經(jīng)理salary匹配起來了跳夭。

但現(xiàn)在是同一個表涂圆，所以做法是同一個表用兩次。本應(yīng)在manager表里的managerid變成這里的id