北京网站建设公司 fim,沈阳网站建设工作,单页静态网站怎么做,wordpress 众筹模板文章目录一 SQL训练资料二 SQL知识点总结1.SQL语句的执行顺序2.窗口函数3.字符串处理函数模糊查询三 SQL题目的总结一 SQL训练资料
牛客SQL题目 猴子数据分析题目 关注的公众号 猴子数据分析
二 SQL知识点总结
1.SQL语句的执行顺序
每一个子句产生的中间结果供接下来的子句…
文章目录一 SQL训练资料二 SQL知识点总结1.SQL语句的执行顺序2.窗口函数3.字符串处理函数模糊查询三 SQL题目的总结一 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.number100--然后筛选出number大于100的数据
group by e.gender--group是在where筛选出来的数据之后进行操作
having num100--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_nos.emp_nowhere d.to_date9999-01-01 and s.to_date9999-01-01
) a
where rk1
order by a.dept_no;排序函数 总共有3种形式为
rank() over(
partition by
order by desc
) as 序号函数名组内排序后例子1,2,3rank1,1,3row_number1,2,3dense_rank1,1,2
取值函数 ①要查询的每一个数据根据当前数据的位置确定
之前之后laglead
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_valuelast_valuenth_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):将AB连接起来读取日期字符串的年份、月份
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_date9999-01-01
select d.emp_no,m.emp_no as manager
from dept_emp d inner join dept_manager m
on d.dept_nom.dept_no
where d.emp_no ! m.emp_no and d.to_date9999-01-01 and m.to_date9999-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_nos.emp_no
where d.to_date9999-01-01 and s.to_date9999-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_nos.emp_nowhere d.to_date9999-01-01 and s.to_date9999-01-01
) a
where rk1
order by a.dept_no;③211获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
薪水第二多的员工可能有多个所以先找出第二的薪水的值。 先查一遍唯一值薪水然后找出薪水为此值的员工
select emp_no,salary
from salaries
where
salary
(select distinct salaryfrom salarieswhere to_date9999-01-01order 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_nos.emp_no
and s.salary(select max(salary)from salaries where to_date9999-01-01and salary !(select max(salary)from salaries where to_date9999-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_noemployees.emp_nowhere salaries.from_dateemployees.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_nob.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 ;
