SQL牛客网刷题易错点4

1，with函数
with函数类似建立了一张 临时虚拟表，用于提高执行效率与可读性，用完即销毁
“一次分析，多次使用”

with子句后必须接着select 语句
1个with子句里可以定义多个内存临时表, 而且可以互相使用

with  表名1 as (select...),表名2 as (select...)
select .....

类似于用with as减少嵌套

2,把值拆开substring_index(str,‘,’,1)函数

#str=www.Geekerjun.com
substring_index(str,'.',1)    #结果是：www

substring_index(str,'.',2)    #结果是：www.Geekerjun
#如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容!
#相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：
substring_index(str,'.',-2)  #结果为：Geekerjun.com

3，SQL154

• if(判断条件，满足条件输出该值，不满足则输出该值)
• char_length(str) 计算str的字符长度
• concat(str1,str2) 将str1与str2合并起来

4，正则表达式regexp
根据指定的匹配模式匹配记录中符合要求的特殊字符串。

#查询包含四个或者更多个o的 name 字段
select name from player where name REGEXP 'oooo+';

#查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据
SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

5，SQL
获取每个部门中当前员工薪水最高的相关信息

注意：

• gruop by 之后直接查询emp_no默认取非聚合的第一条
• 先使用group by获得最高salary，再去用最高salary匹配两表返回dept_no,emp_no信息，会存在A部门的最高薪水，等于B部门非最高薪水时，B部门的非最高薪水也会被显示出来的情况

--多条最大salary，全显示出来
--建一个虚拟表把最大salary与对应部门号提取出来，再与原数据进行匹配
--分组后用聚合函数，select出来只能分组标志列，不能带其他列
select r.dept_no,ss.emp_no,r.maxSalary from (
select d.dept_no,max(s.salary)as maxSalary from dept_emp d,salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01' 
and s.to_date='9999-01-01'
group by d.dept_no
)as r,salaries ss,dept_emp dd
where r.maxSalary=ss.salary
and r.dept_no=dd.dept_no
and dd.emp_no=ss.emp_no
and ss.to_date='9999-01-01'
and dd.to_date='9999-01-01'
order by r.dept_no asc

--解法二，多条最大的salary也只会显示一条
--因为解法利用了GROUP BY 默认取非聚合数据的第一条记录，所以先排好序，拿到的emp_no第一条信息，也是与最大salary匹配的
select r.dept_no,r.emp_no,max(r.salary) from (
select d.dept_no,d.emp_no,s.salary from dept_emp d,salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01' 
and s.to_date='9999-01-01'
order by s.salary desc
)as r
group by r.dept_no
order by r.dept_no asc