sjd_

分类

/ 注册

全部文章（共16篇）

统计salary的累计和running_total

-- 法1：聚合窗口函数 select emp_no, salary, sum(salary)over(order by emp_no) as running_total from salaries where to_date = '9999-01-01' -- 法2：自连接+group by s...

2024-11-08

1 54

exists与in的区别

select e.* from employees e where not exists ( select emp_no from dept_emp d where e.emp_no = d.emp_no ) /*必须要有where这句，exists先执行外层，拿进来和内层的比，内...

2024-11-08

1 60

group_concat函数

select dept_no, group_concat(emp_no) as employees from dept_emp group by dept_no group_concat(多个列名or自定义字符串 [Separator '分隔符'（默认为, ）])聚合函数搭配group by使用e...

2024-11-08

1 51

查找字符串中逗号出现的次数

-- length + replace select id, length(string)-length(replace(string, ',', '')) as cnt from strings

2024-11-07

1 81

获取当前薪水第二多的员工及其对应的薪水salary

-- 使用自连接 select e.emp_no, s.salary, last_name, first_name from employees e left join salaries s on e.emp_no = s.emp_no where e.emp_no = ( select s...

2024-11-07

1 66

获取员工当前薪水比其manager当前薪水还高的相关信息

select t1.emp_no as emp_no, t2.emp_no as manager_no, t1.salary as emp_salary, t2.salary as manager_salary from (select d_e.emp_no, s1.salary, d_e.dep...

2024-11-07

1 47