每日刷一刷力扣SQL(八)
185.部门工资前三高的所有员工
考察点：窗口函数的使用
select d.name as Department ,t.name as Employee,t.salary as Salary
from (select* ,DENSE_Rank() over(PARTITION BY departmentId order by salary DESC) as rank_idfrom Employee
) t join Department d on t.departmentId = d.id
where rank_id <=3
其他解法：
SELECTDepartment.NAME AS Department,e1.NAME AS Employee,e1.Salary AS Salary
FROMEmployee AS e1,Department
WHEREe1.DepartmentId = Department.Id AND 3 > (SELECT count( DISTINCT e2.Salary ) FROM Employee AS e2 WHERE e1.Salary < e2.Salary AND e1.DepartmentId = e2.DepartmentId )
ORDER BY Department.NAME,Salary DESC;
1667.修复表中的文字
考察点：字符串处理函数 concat(),upper(),lower(),substring()
select user_id,concat(upper(LEft(name,1)),lower(substring(name,2))) as name
from Users
order by user_id ASCselect user_id,concat(upper(left(name, 1)), lower(right(name, length(name) - 1))) as name
from users
order by user_id1527.患某种疾病的患者
使用SQL匹配正则表达式
常见正则表达式模式
^：匹配字符串的开头。$：匹配字符串的结尾。.：匹配任意单个字符。[abc]：匹配方括号内的任意一个字符。[a-z]：匹配指定范围内的字符。*：匹配前面的字符零次或多次。+：匹配前面的字符一次或多次。?：匹配前面的字符零次或一次。{n}：匹配前面的字符恰好 n 次。|：表示逻辑或，匹配|两侧的任意一个表达式。
select *
from Patients
where conditions REGEXP '^DIAB1|\\sDIAB1' 196.删除重复的电子邮箱
慢查询优化经验的同学会清楚，在实际生产中，面对千万上亿级别的数据，连接的效率往往最高，因为用到索引的概率较高。
delete p1 from Person p1 , Person p2
wherep1.Email = p2.Email and p1.ID > p2.IDa. 从表p1取出3条记录；
b. 拿着第1条记录去表p2查找满足WHERE的记录，代入该条件p1.Email = p2.Email AND p1.Id > p2.Id后，发现没有满足的，所以不用删掉记录1；
c. 记录2同理；
d. 拿着第3条记录去表p2查找满足WHERE的记录，发现有一条记录满足，所以要从p1删掉记录3；
e. 3条记录遍历完，删掉了1条记录，这个DELETE也就结束了。
176.第二高的薪水
思路：使用limit 和 offset
limit n子句表示查询结果返回前n条数据
offset n表示跳过x条语句
limit y offset x 分句表示查询结果跳过 x 条数据，读取前 y 条数据
使用limit和offset，降序排列再返回第二条记录可以得到第二大的值。
selectifNULL((select distinct salary from Employee order by salary DESC limit 1,1),null)
as SecondHighestSalary另一种思路:比薪水最大的还要小的一条数据
select ifNULL(max(salary),NULL) as SecondHighestSalary
from Employee
where salary < (select max(distinct salary) from Employee
)