只想搞钱的斑马很热爱生活

分类

/ 注册

全部文章（共29篇）

题解 | 用with定义两个子句，简化sql语法结构

WITH #with子句只能出现一次 es AS ( SELECT d.*, s.salary FROM dept_emp d LEFT JOIN salarie...

2024-04-23

1 345

题解 | #获取所有非manager员工当前的薪水情况#

select d.dept_no, e.emp_no, s.salary from employees e left join dept_emp d on e.emp_no = d.emp_no left join salaries s on e.em...

2024-04-23

0 178

题解 | 正确使用DENSE_RANK()窗口函数

SELECT emp_no, salary, DENSE_RANK() OVER ( ORDER BY salary DESC ) AS t_rank FROM salaries ORDER BY t_rank...

2024-04-23

0 171

题解 |将三张表用left join关联起来即可

select e.last_name, e.first_name, d.dept_name from employees e left join dept_emp c on e.emp_no = c.emp_no left join departmen...

2024-04-22

0 235

题解 | 用子查询实现order by的功能

SELECT E.emp_no, S.salary, E.last_name, E.first_name FROM employees E LEFT JOIN salaries S ON E.emp_no = S.emp_no JOIN ( ...

2024-04-22

0 196

题解 | avg(s.salary)作为字段名，要加双引号

SELECT T.title, avg(S.salary) AS "avg(s.salary)" FROM titles T JOIN salaries S ON T.emp_no = S.emp_no GROUP BY T.title ORDER BY "...

2024-04-22

0 211

题解 | 三个JOIN,四个条件

select D.dept_no, D.emp_no, S.salary maxSalary FROM dept_emp D LEFT JOIN ( SELECT D.dept_no, MAX(S...

2024-04-22

0 189

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

SELECT E.emp_no, M.emp_no manager FROM dept_emp E JOIN dept_manager M ON E.dept_no = M.dept_no AND E.emp_no != M.emp_no

2024-04-19

0 200

题解 | #查找所有已经分配部门的员工的……

SELECT E.last_name, E.first_name, D.dept_no FROM employees E LEFT JOIN dept_emp D ON E.emp_no = D.emp_no WHERE D.dept_no IS NO...

2024-04-19

0 215