zhang_han666的博客

题目：统计各个部门的工资记录数代码：SELECT dp.dept_no, dp.dept_name, T.MYSUMFROM departments AS dpINNER JOIN ( SELECT de.dept_no, COUNT(*) AS MYSUM FROM dept_emp AS de INNER JOIN salaries AS s ON de.emp_no = s.emp_no GROUP BY de.dept_no) AS TON d

题目：查找在职员工自入职以来的薪水涨幅情况先分别查找入职工资与当前工资，然后二者作差。代码：SELECT cur.emp_no, (cur.salary - pre.salary) AS growthFROM ( SELECT e1.emp_no, s1.salary FROM employees AS e1 INNER JOIN salaries AS s1 ON e1.emp_no = s1.emp_no WHERE e1.hire_date = s1.

题目：查找所有员工的last_name和first_name以及对应的dept_name代码：SELECT e.last_name, e.first_name, dp.dept_nameFROM (employees AS eLEFT JOIN dept_emp AS deON e.emp_no = de.emp_no)LEFT JOIN departments AS dpON de.dept_no = dp.dept_noSELECT e.last_name, e.first_name,

题目：获取当前薪水第二多的员工的emp_no以及其对应的薪水salary题目明确要求不能用order by完成。代码：SELECT e.emp_no, s.salary, e.last_name, e.first_nameFROM employees AS eINNER JOIN salaries AS sON e.emp_no = s.emp_noWHERE s.salary = ( SELECT s1.salary FROM salaries AS s1 JOIN

题目：获取当前薪水第二多的员工的emp_no以及其对应的薪水salary代码：SELECT emp_no, salaryFROM salariesWHERE salary = ( # 要用=，因为查出来是一个数值；多个用in SELECT salary FROM salaries GROUP BY salary ORDER BY salary DESC LIMIT 1,1)下面的代码不严谨，有可能多个人薪水相同SELECT emp_no, sa

题目：统计出当前各个title类型对应的员工当前薪水对应的平均工资代码：SELECT t.title, avg(s.salary)FROM titles AS tINNER JOIN salaries AS sON t.emp_no = s.emp_noGROUP BY t.titleORDER BY avg(s.salary) ASC

题目：查找employees表代码：SELECT * FROM employees AS emWHERE emp_no & 1# WHERE emp_no % 2 = 1AND em.last_name != 'Mary'ORDER BY hire_date DESC判断奇数：按位与 1，即&1。1的二进制是0001，二进制奇数最后一位一定是1。与运算符：1 & 1 = 1, 1 & 0 = 0, 0 & 1 = 0, 0 & 0 = 0；所

题目：获取每个部门中当前员工薪水最高的相关信息代码：SELECT de.dept_no, de.emp_no, sa.salaryFROM dept_emp AS deINNER JOIN salaries AS saON de.emp_no = sa.emp_noWHERE sa.salary IN ( SELECT MAX(sa1.salary) FROM dept_emp AS de1 INNER JOIN salaries AS sa1 ON de1.em

题目：获取所有员工当前的manager代码：SELECT dept_emp.emp_no, dept_manager.emp_no AS managerFROM dept_emp INNER JOIN dept_managerON dept_emp.dept_no = dept_manager.dept_noWHERE dept_emp.emp_no != dept_manager.emp_no

题目：获取所有非manager的员工emp_no代码：SELECT emp_no FROM employeesWHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)

题目：找出所有员工当前具体的薪水salary情况代码：SELECT salary FROM salariesGROUP BY salary ORDER BY salary DESC;

题目：查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t代码：SELECT emp_no, COUNT(*) tFROM salariesGROUP BY emp_noHAVING t > 15;

题目：查找所有员工的last_name和first_name以及对应部门编号dept_no代码：SELECT last_name, first_name, dept_noFROM employees LEFT JOIN dept_empON employees.emp_no = dept_emp.emp_no;

题目：查找所有已经分配部门的员工的last_name和first_name代码：SELECT last_name, first_name, dept_noFROM employees INNER JOIN dept_empON employees.emp_no = dept_emp.emp_no;

题目：牛客 SQL3代码：SELECT salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_no FROM salaries INNER JOIN dept_managerON salaries.emp_no = dept_manager.emp_noORDER BY salaries.emp_no;

题目：查找入职员工时间排名倒数第三的员工所有信息代码：SELECT * FROM employeesWHERE hire_date = ( SELECT hire_date FROM employees ORDER BY hire_date DESC LIMIT 1 OFFSET 2);

题目：查找最晚入职员工的所有信息代码：SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees)