一 SQL训练资料

牛客SQL题目
猴子数据分析题目
关注的公众号
猴子数据分析

二 SQL知识点总结

1.SQL语句的执行顺序

每一个子句产生的中间结果供接下来的子句使用（阶段性）

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->SELECT子句->ORDER BY子句->LIMIT子句->最终结果

select e.number,count(e.name) as num
from employees e  --（先从表格拿数据）
where  e.number>100--（然后筛选出number大于100的数据）
group by e.gender--（group是在where筛选出来的数据之后进行操作）
having  num>100--（having对group by  产生的数据进行筛选）
order by num desc  --(对group筛选出的数据进行排序)
limit 0,1;--（limit对最后）

2.窗口函数

将聚合的数据放到原数据的后方

参考资料：
窗口函数总结
窗口函数

查询每个部门的当前的最高薪水情况
薪水可以聚类max，但是员工号不可以，所以得用窗口函数

--部门、员工号、薪水
select a.dept_no,a.emp_no,a.salary 
from
(select d.dept_no,d.emp_no,s.salary,rank() over(partition by d.dept_noorder by  s.salary desc) as rkfrom dept_emp d inner join salaries son d.emp_no=s.emp_nowhere d.to_date='9999-01-01' and s.to_date='9999-01-01'
) a
where rk=1
order by a.dept_no;

排序函数
总共有3种，形式为

rank() over(
partition by
order by    desc
) as 

序号函数名	组内排序后例子（1,2,3）
rank	1,1,3
row_number	1,2,3
dense_rank	1,1,2

取值函数
①要查询的每一个数据，根据当前数据的位置确定

之前	之后
lag	lead

lag/lead() over()的使用lag(col,n,default):用于统计窗口往上第n行值：第一个参数为列名；第二个参数为往上第n行（默认为1）；第三个参数为默认值（当往上第n行为null的时候，取默认值，如果不指定，则取null）。同理：lead(col,n,default):用于统计窗口往下第n行值：

例：找到车辆上一次的锁车记录
那么首先根据锁车的时间排序（降序），然后在这次锁车的时间的前一个

select fence,bike_id,unlock_time, -- 开锁lock_time, -- 锁车lag(lock_time,1,null)  over(partition by fence,hour(unlock_time),bike_id) as last_lock_time
from bike_hour_inc

②根据在over（）窗口中的位置确定

第一个	最后一个	第n个
first_value	last_value	nth_value

以下语句统计了不同产品最低销售额、最高销售额以及第三高销售额所在的月份：
不同产品最高销售额所在月份
不同产品（分组），最高销售额（排序），所在月份（第一行）

  SELECT product AS "产品", ym AS "年月",amount AS "销售额",FIRST_VALUE(m.ym) OVER (PARTITION BY m.product ORDER BY m.amount DESCROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "最高销售额月份",LAST_VALUE(m.ym) OVER (PARTITION BY m.product ORDER BY m.amount DESCROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "最低销售额月份",NTH_VALUE(m.ym,3) OVER (PARTITION BY m.product ORDER BY m.amount DESCROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "第三高销售额月份"FROM sales_monthly mORDER BY product, ym;

3.字符串处理函数

字符串拼接，截取函数

将A的前两个字符与B的前3个字符拼接起来组成D，最后全部变为大写

upper(concat(substring(cust_contact, 1, 2), substring(cust_city, 1, 3))) as   user_login
upper()
substring(A,1,2):对A从第一个字母开始，选择2个字母
contract(A,B):将A，B连接起来

读取日期字符串的年份、月份

year()
month()

模糊查询

占位符

%	_
任意（0~∞）个字符	任意1个字符

查找包含toy的名字

select name from A where  name like  "%toy%"

查找以toy作为第二个字符的名字

select name from A  where name like "_toy%"

查找以toy作为结尾、或者开头的名字

select name from A where (name like "%toy")   | (name like "toy%")

三 SQL题目的总结

①205所有员工当前的manager

题目拆解，当前（where时间上进行筛选），所有员工（左外连接）

---所有员工（左外连接），当前（筛选to_date='9999-01-01'）
select d.emp_no,m.emp_no as manager
from dept_emp d inner join dept_manager m
on d.dept_no=m.dept_no
where d.emp_no != m.emp_no and d.to_date='9999-01-01' and m.to_date='9999-01-01';

②206题获取每个部门当前员工最大薪水信息

需要用到group by （分组函数）

错误实例

select d.dept_no,d.emp_no,max(s.salary) as maxSalary
from dept_emp d inner join salaries s
on d.emp_no=s.emp_no
where  d.to_date='9999-01-01' and s.to_date='9999-01-01'
group by d.dept_no
order by d.dept_no asc;--这里，对于分组得到的每一列数据都需要进行聚合，
--首先按部门分组，那么部门号是聚合了的
--然后最大薪水是对组内的薪水进行了聚合
--而员工号不能进行聚合，所以结果错误

正确实例

select a.dept_no,a.emp_no,a.salary 
from
(select d.dept_no,d.emp_no,s.salary,rank() over(partition by d.dept_noorder by  s.salary desc) as rkfrom dept_emp d inner join salaries son d.emp_no=s.emp_nowhere d.to_date='9999-01-01' and s.to_date='9999-01-01'
) a
where rk=1
order by a.dept_no;

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

薪水第二多的员工可能有多个，所以先找出第二的薪水的值。
先查一遍唯一值薪水，然后找出薪水为此值的员工

select emp_no,salary
from salaries
where
salary=
(select distinct salaryfrom salarieswhere to_date='9999-01-01'order by  salary desclimit 1,1
)

④212获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
不能用order by 语句
找出当前薪水第二多的薪水，先找出最大的，然后排除最大的再找一次

select e.emp_no,s.salary,e.last_name,e.first_name
from employees e ,salaries s
where e.emp_no=s.emp_no
and s.salary=(select max(salary)from salaries where to_date='9999-01-01'and salary !=(select max(salary)from salaries where to_date='9999-01-01'))and s.to_date ='9999-01-01';

215查找在职员工自入职以来的薪水涨幅情况

在职员工薪水涨幅情况，涉及两个极端情况
分别查极端，然后内连接构建新表，最后计算得出结果
入职的薪水：入职日期等于雇佣日期的薪水
现在的薪水：雇佣日期为现在

SELECT s1.emp_no AS "emp_no", s2.salary - s1.salary AS "growth"
FROM (SELECT salaries.emp_no, salaryFROM salaries inner join employeeson salaries.emp_no=employees.emp_nowhere salaries.from_date=employees.hire_date
) s1
INNER JOIN (SELECT emp_no, salaryFROM salariesWHERE to_date = '9999-01-01'
) s2 ON s1.emp_no = s2.emp_no -- 因为INNER JOIN只会连接匹配行，所以s2中筛除的已离职员工则不会被显示
ORDER BY growth ASC
;

216统计各个部门的工资记录数

首先
对于对各个部门的工资记录数进行聚类运算，注意对于所有工资进行统计，没有部门的排除（左外连接），对部门号进行分组统计。
然后
对于部门名称进行表连接

select  a.dept_no,a.dept_name,b.sum
from departments a
inner join
( select  d.dept_no,count(*) as sumfrom dept_emp d right join salaries son  d.emp_no =s.emp_nowhere d.dept_no is not nullgroup by d.dept_no
) b
on a.dept_no=b.dept_no
order by a.dept_no  asc;

217对所有员工的薪水按照salary降序进行1-N的排名

利用dense_rank() over ()窗口函数
首先得到每一行的排名，然后最后输出数据时要进行降序排序

SELECT emp_no,salary,dense_rank () over (
ORDER BY salary DESC) AS `rank` 
FROMsalaries 
WHERE to_date = '9999-01-01' ;