牛客593719959号

题解

/ 注册

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

题解 | #刷题通过的题目排名#

sql script select id,number,dense_rank() over(order by number desc) as t_rank from passing_number;

sql

2021-09-07

1 356

题解 | #对于employees表中，给出奇数行的first_name#

sql script select e.first_name from (select rank() over(order by first_name) as rank_fn,first_name from employees) tmp, employees e where rank_f...

sql

2021-09-07

3 518

题解 | #统计salary的累计和running_total#

sql script select emp_no, salary, sum(salary) over(order by emp_no ) as running_total from salaries where to_date='9999-01-01'

sql

2021-09-07

0 328

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

sql script select e.emp_no, e.first_name, e.last_name, eb.btype, s.salary, case when eb.btype=1 then s.salary*0.1 when eb.btype=2 then s.salar...

sql

2021-09-07

0 338

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

sql script select * from employees where not exists(select emp_no from dept_emp where dept_emp.emp_no=employees.emp_no)

sql

2021-09-07

0 456

题解 | #查找排除当前最大、最小salary之后的员工的平均工资avg_salary#

sql script select (sum(salary)-max(salary)-min(salary))/(count(*)-2) as avg_salary from salaries where to_date='9999-01-01' group by to_date

sql

2021-09-06

0 273

题解 | #获取Employees中的first_name#

sql script select first_name from employees order by substring(first_name, length(first_name)-1,length(first_name))

sql

2021-09-06

0 352

题解 | #将所有to_date为9999-01-01的全部更新为NULL#

sql script update titles_test set to_date=NULL, from_date='2001-01-01' where to_date='9999-01-01'

sql

2021-09-06

0 271

题解 | #删除emp_no重复的记录，只保留最小的id对应的记录。#

sql script delete from titles_test where id in (select * from (select max(id) as id from titles_test group by emp_no having count(*)>1) as tmp);

sql

2021-09-06

0 309

题解 | #构造一个触发器audit_log#

sql script create trigger audit_log after insert on employees_test for each row begin insert into audit select id,name from employees_test; end;

sql

2021-09-06

0 332