Lateral

分类

/ 注册

全部文章（共102篇）

题解 | 统计salary的累计和running_total

select emp_no,salary, sum(salary) over(order by emp_no rows between unbounded preceding and current row) as running_total from salaries where to_date...

2025-06-06

0 23

题解 | 获取有奖金的员工相关信息。

select b.emp_no, e.first_name, e.last_name, b.btype, s.salary, case when b.btype=1 then round(s.salary*0.1,1) when b.btype=2 then round(s.sal...

2025-06-06

0 20

题解 | 使用含有关键字exists查找未分配具体部门的员工的所有信息。

select * from employees e where not exists ( select 1 from dept_emp d where e.emp_no=d.emp...

2025-06-06

0 22

题解 | 平均工资

select avg(salary) as avg_salary from salaries where (to_date='9999-01-01' and salary<>(select max(salary) from salaries...

2025-06-03

0 29

题解 | 按照dept_no进行汇总

select dept_no, group_concat(emp_no) as employees from dept_emp group by dept_no

2025-06-03

0 26

题解 | 获取employees中的first_name

select a.first_name from (select *, substr(first_name,length(first_name)-1,1) as last_but_two_word, substr(first_name,length(first_name),1) as last_wo...

2025-06-03

0 20

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

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

2025-06-03

0 21

题解 | 将所有获取奖金的员工当前的薪水增加10%

update salaries set salaries.salary=salaries.salary*1.1 where salaries.to_date='9999-01-01' and salaries.emp_no in(select emp_no from emp...

2025-05-24

0 29

题解 | 在audit表上创建外键约束，其emp_no对应employees_test表的主键id

alter table audit add constraint fk foreign key (emp_no) references employees_test(id) on delete cascade on update cascade

2025-05-24

0 26

题解 | 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

update titles_test set emp_no=replace(emp_no,10001,10005) where id=5 and emp_no=10001

2025-05-24

0 21