目錄
1 查找最晚入職員工的資訊
2 查找入職第三晚的員工資訊
3 查找目前薪水詳情及部門編号
4 查找所有員工入職時的薪水情況
5 查找已配置設定員工姓名
6 查找員工姓名
7 查找漲薪找過15次的員工
8 找出所有員工目前薪水情況
9 擷取目前部門所有manager的薪水情況
10 擷取所有非manager的員工emp_no
11 擷取目前員工的manager
12 擷取目前薪水最高的員工資訊
注：下面的解題中會發現後多INNER JOIN這種内聯，其實不用寫也沒關系的。
1 查找最晚入職員工的資訊
題目描述
查找最晚入職員工的所有資訊
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));輸入描述:
無輸出描述:
示例1
輸入
無
輸出
無
1 -- 查詢語句 LIMIT 1 是LIMIT 0,1 表是從第0條資料開始取1條資料
2 SELECT * FROM employees ORDER BY hire_date DESC LIMIT 1;
3 -- 這個語句有問題在于隻找到了一條記錄，有可能當天會有好多人入職
4
5 -- 從表的日期中挑選出最大的就可以避免上述問題了
6 SELECT * FROM employees WHERE
7 hire_date=(SELECT MAX(hire_date) FROM employees) 其實對于max也可以用下面的代替：這個和下面查找第三晚的就很類似了
SELECT *
FROM employees
WHERE hire_date=(SELECT hire_date FROM employees ORDER BY hire_date DESC LIMIT 1) 2 查找入職第三晚的員工資訊
在上面一個問題的前提下找到第三晚入職的員工
1 -- distinct 是過濾掉重複的，因為前面的可能日期相同的不止一個，但這樣同樣會有一個問題
2 -- 采用下面的方法也是隻能挑出來一個資訊，當倒數第三個日期有好幾個時就會出現錯誤
3 SELECT *FROM employees WHERE
4 hire_date=(SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2,1); 3 查找目前薪水詳情及部門編号
題目描述
查找各個部門目前(to_date='9999-01-01')上司目前薪水詳情以及其對應部門編号dept_no輸入描述:
無輸出描述:
1 -- 根據輸出表的資訊确定salaries是主表
2 SELECT s.*,d.dept_no -- 選擇salaries表和dept_manager的dept_no列
3 FROM salaries AS s JOIN dept_manager AS d -- 兩個as相當于對表重命名，是以一開始才可以直接用s和d表示兩個表
4 ON s.emp_no=d.emp_no -- on在建立臨時表的限制條件，一般兩個表連結時多用
5 WHERE s.to_date='9999-01-01' AND d.to_date='9999-01-01' -- 臨時表生成後的限制
6 -- 執行順序為先 on再where
7
8 -- 可以用下面這個代碼試試會産生什麼情況
9 SELECT d.*,s.salary FROM salaries AS s JOIN dept_manager AS d
10 ON s.emp_no=d.emp_no
11 WHERE s.to_date='9999-01-01' AND d.to_date='9999-01-01' 上面的代碼但在實際開發中是不推薦使用的，因為資料庫在使用中應盡量減少實體連接配接，使用邏輯連接配接，是以多使用where語句來完成關聯，此外使用where在性能上也要比使用join連接配接快
SELECT s.*,d.dept_no
FROM salaries AS s, dept_manager AS d
WHERE s.emp_no = d.emp_no
AND s.to_date='9999-01-01'
AND d.to_date='9999-01-01' 4 查找所有員工入職時的薪水情況
題目描述
查找所有員工入職時候的薪水情況，給出emp_no以及salary， 并按照emp_no進行逆序
輸入描述:
無輸出描述:
1 /*
2 此題應注意以下四個知識點：
3 1、由于測試資料中，salaries.emp_no 不唯一(因為号碼為 emp_no 的員工會有多次漲薪的可能，是以在 salaries 中對應的記錄不止一條)，employees.emp_no 唯一，即 salaries 的資料會多于 employees，是以需先找到 employees.emp_no 在 salaries 表中對應的記錄salaries.emp_no，則有限制條件 e.emp_no = s.emp_no
4 2、根據題意注意到 salaries.from_date 和 employees.hire_date 的值應該要相等，是以有限制條件 e.hire_date = s.from_date
5 3、根據題意要按照 emp_no 值逆序排列，是以最後要加上 ORDER BY e.emp_no DESC
6 4、為了代碼良好的可讀性，運用了 Alias 别名語句，将 employees 簡化為 e，salaries 簡化為s，即 employees AS e 與 salaries AS s，其中 AS 可以省略
7 */
8
9 SELECT e.emp_no,s.salary FROM employees AS e INNER JOIN salaries AS s -- JOIN In 就是JOIN内連接配接
10 ON e.emp_no=s.emp_no AND e.hire_date=s.from_date -- 要找到是入職資訊
11 ORDER BY e.emp_no DESC -- 降序排列
12
13 # 方法二：直接用逗号并列查詢兩張表
14 SELECT e.emp_no, s.salary FROM employees AS e, salaries AS s
15 WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date
16 ORDER BY e.emp_no DESC 5 查找已配置設定員工姓名
題目描述
查找所有已經配置設定部門的員工的last_name和first_name
輸入描述:
無
輸出描述:
直接查詢即可：
SELECT e.last_name,e.first_name,d.dept_no
FROM dept_emp AS d, employees AS e
WHERE d.emp_no = e.emp_no 下面是看題目讨論中給出的一些方法，可以參考一下
1 -- 采用内連接配接的方式
2 SELECT e.last_name,e.first_name,d.dept_no FROM dept_emp AS d
3 INNER JOIN employees AS e
4 ON e.emp_no = d.emp_no;
5
6 -- 但此問題隻要用自然連接配接就行了，兩張表隻有一列相同且屬性也相同
7
8 SELECT e.last_name, e.first_name, d.dept_no
9 FROM dept_emp d NATURAL JOIN employees e; 6 查找員工姓名
題目描述
查找所有員工的last_name和first_name以及對應部門編号dept_no，也包括展示沒有配置設定具體部門的員工
輸入描述:
無
輸出描述:
1 /*
2 INNER JOIN 兩邊表同時有對應的資料，即任何一邊缺失資料就不顯示。
3 LEFT JOIN 會讀取左邊資料表的全部資料，即便右邊表無對應資料。
4 RIGHT JOIN 會讀取右邊資料表的全部資料，即便左邊表無對應資料。
5 */
6
7 SELECT e.last_name, e.first_name, d.dept_no
8 FROM employees e
9 LEFT JOIN dept_emp d
10 ON e.emp_no = d.emp_no 7 查找漲薪找過15次的員工
題目描述
查找薪水漲幅超過15次的員工号emp_no以及其對應的漲幅次數t輸入描述:
無輸出描述:
1 /*
2 此題應注意以下四點：
3 1、用COUNT()函數和GROUP BY語句可以統計同一emp_no值的記錄條數
4 2、根據題意，輸出的漲幅次數為t，故用AS語句将COUNT(emp_no)的值轉換為t
5 3、由于COUNT()函數不可用于WHERE語句中，故使用HAVING語句來限定t>15的條件
6 4、最後存在一個了解誤區，漲幅超過15次，salaries中相應的記錄數應該超過16(從第2條記錄開始算作第1次漲幅)，不過題目為了簡單起見，将第1條記錄當作第1次漲幅，是以令t>15即可
7 注意： 嚴格來說，下一條salary高于本條才算漲幅，但本題隻要出現了一條記錄就算一次漲幅，salary相同可以了解為漲幅為0，salary變少了解為漲幅為負
8 */
9 -- group by 可以簡單了解為分類彙總
10 SELECT emp_no, COUNT(emp_no) AS t FROM salaries
11 GROUP BY emp_no HAVING t > 15 8 找出所有員工目前薪水情況
題目描述
找出所有員工目前(to_date='9999-01-01')具體的薪水salary情況，對于相同的薪水隻顯示一次,并按照逆序顯示輸入描述:
無輸出描述:
1 /*
2 對于distinct,groupby的性能。
3 資料量非常巨大時候，比如1000萬中有300W重複資料，這時候的distinct的效率略好于group by；
4 對于相對重複量較小的資料量比如1000萬中1萬的重複量，用groupby的性能會遠優于distnct
5 */
6
7 SELECT salary FROM salaries WHERE to_date='9999-01-01' GROUP BY salary ORDER BY salary DESC;
8 -- 方法二：
9 SELECT DISTINCT salary FROM salaries WHERE to_date = '9999-01-01' ORDER BY salary DESC 9 擷取目前部門所有manager的薪水情況
題目描述
擷取所有部門目前manager的目前薪水情況，給出dept_no, emp_no以及salary，目前表示to_date='9999-01-01'輸入描述:
無輸出描述:
自己寫的：
SELECT d.dept_no,d.emp_no,s.salary
FROM salaries AS s,dept_manager AS d
WHERE s.to_date='9999-01-01'
AND d.to_date='9999-01-01'
AND d.emp_no = s.emp_no 1 SELECT d.dept_no, d.emp_no, s.salary
2 FROM salaries AS s INNER JOIN dept_manager AS d
3 ON d.emp_no = s.emp_no
4 AND d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' -- 限制目前時間 1 -- 下面是讨論中的内容，但我試了下沒有order by到也通過了，可能這個本來就不是重要問題吧
2 -- 将連接配接語句改成 FROM dept_manager AS d INNER JOIN salaries AS s 後，結果通不過
3 -- 連接配接後按照前面的第一個 KEY 值排序，若 salaries 在前，則按照 s.emp_no 排序(因為限制條件為 d.emp_no = s.emp_no，
4 -- 是以對 s.emp_no 排序就是對d.emp_no 排序)，輸出跟參考答案一緻，沒問題；
5 -- 若 dept_manager 在前，則按照 d.dept_no排序，此時與參考答案不同，是以需要在末尾手動用 ORDER BY 對d.emp_no進行排序。
6
7 SELECT d.dept_no, d.emp_no, s.salary
8 FROM dept_manager AS d INNER JOIN salaries AS s
9 ON d.emp_no = s.emp_no
10 AND d.to_date = '9999-01-01'
11 AND s.to_date = '9999-01-01'
12 ORDER BY d.emp_no 10 擷取所有非manager的員工emp_no
題目描述
擷取所有非manager的員工emp_no輸入描述:
無輸出描述:
1 -- 方法一：使用NOT IN選出在employees但不在dept_manager中的emp_no記錄
2 SELECT emp_no FROM employees
3 WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
4 -- 方法二：先使用LEFT JOIN連接配接兩張表，再從此表中選出dept_no值為NULL對應的emp_no記錄
5
6 SELECT emp_no FROM (SELECT * FROM employees LEFT JOIN dept_manager
7 ON employees.emp_no = dept_manager.emp_no)
8 WHERE dept_no IS NULL
9 -- 方法三：方法二的簡版，使用單層SELECT語句即可
10
11 SELECT employees.emp_no FROM employees LEFT JOIN dept_manager
12 ON employees.emp_no = dept_manager.emp_no
13 WHERE dept_no IS NULL
14
15 -- 方法四：使用集合運算 EXPECT 集合差運算 UNION 集合并運算 INTERSECT 集合交運算
16 SELECT employees.emp_no
17 FROM salaries
18 EXCEPT
19 SELECT dept_manager.emp_no
20 FROM dept_manager; 11 擷取目前員工的manager
題目描述：
擷取所有員工目前的manager，如果目前的manager是自己的話結果不顯示，目前表示to_date='9999-01-01'。
結果第一列給出目前員工的emp_no,第二列給出其manager對應的manager_no。輸入描述:
無輸出描述:
1 /*
2 本題應注意以下三點：
3 1、用 INNER JOIN 連接配接兩張表，因為要輸出自己的經理，得知自己與經理的部門要相同，故有限制條件 de.dept_no = dm.dept_no
4 2、再用 WHERE 限制目前員工與目前經理的條件，即 dm.to_date 等于 '9999-01-01' 、de.to_date 等于 '9999-01-01' 、 de.emp_no 不等于 dm.emp_no
5 3、為了增強代碼可讀性，将 dept_emp 用别名 de 代替，dept_manager 用 dm 代替，最後根據題意将 de.emp_no 用别名 manager_no 代替後輸出
6 */
7 SELECT de.emp_no, dm.emp_no AS manager_no
8 FROM dept_emp AS de INNER JOIN dept_manager AS dm
9 ON de.dept_no = dm.dept_no
10 WHERE dm.to_date = '9999-01-01' AND de.to_date = '9999-01-01' AND de.emp_no <> dm.emp_no 在上面代碼中也可以不用使用内聯 INNER JOIN的。
12 擷取目前薪水最高的員工資訊
題目描述
擷取所有部門中目前員工薪水最高的相關資訊，給出dept_no, emp_no以及其對應的salary輸入描述:
無輸出描述:
1 SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary
2 FROM salaries AS s INNER JOIN dept_emp AS d
3 ON d.emp_no = s.emp_no
4 WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
5 GROUP BY d.dept_no
6
7 /*
8 有同學提出疑問，如果存在多條最大記錄怎麼辦？而 MAX 函數根據不同資料庫隻選擇最前一條或最後一條最大記錄，其餘記錄均被忽略。此時解法如下：
9 1、建立兩張表，一張為maxsalary，用于存放目前每個部門薪水的最大值；另一張為currentsalary，用于存放目前每個部門所有員工的編号和薪水；
10 2、限定條件為兩張表的 dept_no 和 salary 相等，這樣就可以找出目前每個部門所有薪水等于最大值的員工的相關資訊了；
11 3、最後記得根據 currentsalary.dept_no 升序排列，輸出與參考答案相同的記錄表。
12 4、以下代碼雖然很長，仔細一看都是基于上面的基礎解法變化而來的，中心思想就是繞開 MAX 的特性限制，運用比較的方法選出多個相同的最大值。
13 */
14
15 SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary
16 FROM
17 #建立maxsalary表用于存放目前每個部門薪水的最大值
18 (SELECT d.dept_no, MAX(s.salary) AS salary
19 FROM salaries AS s INNER JOIN dept_emp AS d
20 ON d.emp_no = s.emp_no
21 WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
22 GROUP BY d.dept_no) AS maxsalary,
23 #建立currentsalary表用于存放目前每個部門所有員工的編号和薪水
24 (SELECT d.dept_no, s.emp_no, s.salary
25 FROM salaries AS s INNER JOIN dept_emp AS d
26 ON d.emp_no = s.emp_no
27 WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
28 ) AS currentsalary
29 #限定條件為兩表的dept_no和salary均相等
30 WHERE currentsalary.dept_no = maxsalary.dept_no
31 AND currentsalary.salary = maxsalary.salary
32 #最後以currentsalary.dept_no排序輸出符合要求的記錄表
33 ORDER BY currentsalary.dept_no