SQL练习11-牛客网51-60题

链接：www.nowcoder.com/ta/sql?page…

$\color{red}{第51题}$ 查找字符串中字符出现的次数
$\color{red}{第52题}$ substr
$\color{red}{第53题}$ group_concat
$\color{red}{第55题}$ 分页查询
$\color{red}{第57题}$ 使用含有关键字exists
$\color{red}{第59题}$ case when
$\color{red}{第60题}$ 累计求和

51. 查找字符串'10,A,B' 中逗号','出现的次数cnt。

select
length('10,A,B')
-
length(replace('10,A,B',',',''))
cnt

52. 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
    CREATE TABLE employees (
    emp_no int(11) NOT NULL,
    birth_date date NOT NULL,
    first_name varchar(14) NOT NULL,
    last_name varchar(16) NOT NULL,
    gender char(1) NOT NULL,
    hire_date date NOT NULL,
    PRIMARY KEY (emp_no));

select first_name
from employees
order by substr(first_name,-2,2)

53. 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
    CREATE TABLE dept_emp (
    emp_no int(11) NOT NULL,
    dept_no char(4) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no,dept_no));

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

54. 查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary。 CREATE TABLE salaries (
    emp_no int(11) NOT NULL,
    salary int(11) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no,from_date));

错误：

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

一直提示没有通过全部用例

查看通过的代码发现应该是这样的：

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

逻辑应该是：剔除掉的最大、最小salary也应该是当前的。

55. 分页查询employees表，每5行一页，返回第2页的数据
    CREATE TABLE employees (
    emp_no int(11) NOT NULL,
    birth_date date NOT NULL,
    first_name varchar(14) NOT NULL,
    last_name varchar(16) NOT NULL,
    gender char(1) NOT NULL,
    hire_date date NOT NULL,
    PRIMARY KEY (emp_no));

select * from employees limit 5,5

• LIMIT 接受一个或两个数字参数。
• 参数必须是一个整数常量。
• 如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。
• 如 SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
• 为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： 如 SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
• 如果只给定一个参数，它表示返回最大的记录行数目： SELECT * FROM table LIMIT 5; //检索前 5 个记录行。换句话说，LIMIT n 等价于 LIMIT 0,n。

56. 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，没有分配奖金的员工不显示对应的bonus类型btype和received

select e.emp_no, dept_no, btype, received
from employees e
join dept_emp de on e.emp_no = de.emp_no
left join emp_bonus eb on e.emp_no = eb.emp_no

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

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

59. 获取有奖金的员工相关信息。给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'

select e.emp_no, e.first_name, e.last_name, eb.btype, s.salary, 
case eb.btype when 1 then s.salary*0.1
              when 2 then s.salary*0.2
              else s.salary*0.3
end bonus
from employees e
join emp_bonus eb on e.emp_no = eb.emp_no
join salaries s on e.emp_no = s.emp_no
where s.to_date='9999-01-01'

60. 按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和，其他以此类推。

select s1.emp_no, s1.salary, 
(select sum(s2.salary) from salaries s2 where s2.emp_no<=s1.emp_no and s2.to_date = '9999-01-01') running_total
from salaries s1
where s1.to_date = '9999-01-01'
order by s1.emp_no