YishanZ

题解

/ 注册

全部文章 / 题解（共72篇）

SQL23题解 | #对所有员工的薪水按照salary降序进行1-N的排名#

考察 DENSE_RANK() 的应用 SELECT emp_no, salary, &...

Mysql

2022-01-09

0 345

SQL22题解 | #统计各个部门的工资记录数#

3 个表的连接 1. 一般先关联表1，表2 2. 得出关联后，再联表3 #关联1，2 SELECT d.dept_no, d.dept_name, COUNT(s.salary) AS 'sum' FROM&n...

Mysql

2022-01-09

0 300

SQL21题解 | #查找在职员工自入职以来的薪水涨幅情况#

薪酬涨幅 = 现在的薪酬 - 入职时的薪酬需要排除已经离职的人员（to_date = '9999-01-01') 1. 先求入职时的薪酬，建立带有入职薪酬的表 2. 再建现在薪酬表，将已经离职的排除 3. 将2个薪酬相减，得出结果 4. 涨幅升序排序 ...

Mysql

2022-01-09

0 256

题解 | #查找所有员工的last_name和first_name以及对应的dept_name#

SELECT e.last_name, e.first_name, d.dept_name FROM employees AS e LEFT JOIN dept_emp AS de ON e...

Mysql

2022-01-09

0 225

题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

SELECT e.emp_no, s.salary, e.last_name, e.first_name FROM employees AS e INNER JOIN salaries AS s ON e.emp_no = s.emp_no WHERE s.salary = (SELECT MAX(...

Mysql Sqlite

2022-01-09

0 293

题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

SELECT emp_no, salary FROM salaries WHERE salary = (SELECT DISTINCT salary FROM salaries ORDER BY salary DESC ...

Mysql

2022-01-09

0 242

题解 | #统计出当前各个title类型对应的员工当前薪水对应的平均工资#

SELECT t.title, AVG(s.salary) FROM titles AS t, salaries AS s WHERE t.emp_no = s.emp_no GROUP BY t.title ORDER BY AVG(s.salary) ASC

Mysql

2022-01-09

0 208

题解 | #获取所有员工当前的manager#

SELECT e.emp_no, m.emp_no FROM dept_emp AS e INNER JOIN dept_manager AS m ON e.dept_no = m.dept_no WHERE e.emp_no != m.emp_no;

Mysql

2022-01-08

1 239

题解 | #查找所有员工的last_name和first_name以及对应部门编号dept_no#

SELECT e.last_name, e.first_name, d.dept_no FROM employees AS e LEFT JOIN dept_emp AS d ON e.emp_no = d.emp_no

Mysql

2022-01-08

1 234

题解 | #查找所有已经分配部门的员工的last_name和first_name以及dept_no#

SELECT e.last_name, e.first_name, d.dept_no FROM employees AS e, dept_emp AS d WHERE e.emp_no = d.emp_no AND d.dept_no IS NOT NULL;

Mysql

2022-01-08

1 298