前言
中午吃饭的时候无意中在QQ群里突然看到一道有趣的面试题，直到午休结束群里都没人回复第二道题的答案，可能是因为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
