前言
中午吃飯的時候無意中在QQ群里突然看到一道有趣的面試題，直到午休結(jié)束群里都沒人回復(fù)第二道題的答案，可能是因為sql太長悔据，大家懶得寫了殉簸，遂開一篇文章記錄一下闰集，題目如下：
題目.png
答案
- 1.簡單的update語句，需要注意的是員工表中的age和salary都是int型
UPDATE tbEmployee
SET salary = salary + 200
WHERE
age >= 40
AND salary < 2000;
- 2.涉及多表查詢及子查詢般卑，可能我的sql寫的有點長
WITH emp AS (
SELECT
tbD. NAME,
tbE. ID
FROM
tbDepartmemtEmployee tbDE
LEFT JOIN tbEmployee tbE ON tbDE.employeeId = tbE. ID
LEFT JOIN tbDepartment tbD ON tbDE. ID = tbD. ID
WHERE
tbE.age >= 40
AND tbE.sqlary < 2000
) SELECT
emp. NAME,
COUNT (emp. NAME)
FROM
emp
GROUP BY
emp. NAME
ORDER BY
COUNT (emp. NAME) DESC
