Leetcode SQL 刷题 Mysql

【1】627. 交换工资

case when

Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

模板：

case 
when expression_1 then 1
when expression_2 then 2
else 3
end; --end don't forget!!!

参考链接：https://www.cnblogs.com/shaopang/p/6903985.html

update更新语句模板：

update table_name set column_name=values when expression

IF 使用  参考：https://www.cnblogs.com/xuhaojun/p/9141396.html

if(exp1,exp2,exp3)   exp1为真 则 exp2 否则 exp3

答案： 

UPDATE salary 
SET 
   sex = 
   CASE when sex='m' then 'f'
   else 'm'
END;

UPDATE salary  SET sex=IF(sex='m','f','m');

update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

 第三种解答方法中利用到了 本身列的sex值，可以这样写。比如将所有 dpart_no =1的部门每月发放的薪水提高10。

UPDATA salary SET salary = salary+10 where depart_no = 1

【2】176. 第二高的薪水

从这道题，举一反三到求解第N高问题。 窗口函数？？：https://www.cnblogs.com/sirc/archive/2010/06/28/1766981.html

limit 三种用法：https://blog.csdn.net/cnwyt/article/details/81945663

limit i --读取前i条数据 
limit 0,i -- 同上
limit i,n -- 从index=i位置开始，读取n条数据 
limit n offset i -- 同上 index 从0开始计算,读取时,include i 

ifnull(exp1,epx2)  exp1 不为null,则返回exp1,否则返回exp2

distinct 去重，不要用group by 做。

判断是否为空时，where中利用 is null，不要用 =NULL

select ifnull((select  distinct  Salary from Employee
order by Salary desc limit 1 offset 1 ),null)  as  SecondHighestSalary
-- use the function ifnull()


select max(Salary) as SecondHighestSalary   --小于最高的薪水中最高的，那么就是第二高
from Employee 
where Salary< (
    select max(Salary)
    from Employee
)

select ( 
select distinct Salary --将这个查询作为临时表,从这个表中获取
from Employee
order by  Salary desc
limit 1,1) as SecondHighestSalary

【3】1179. 重新格式化部门表

这道题是一道经典的将列转行的问题。利用case when 可以达到要求。但是有一个问题，就是为什么在 case when 前面加 sum()等聚合函数。

加sum()聚合函数的意义在于：gruopy by 会将id相同的，整合在一行中。并且case when 不会循环去检验每条语句，它只检验一条语句，因为 当 month = "Feb"时，第一条为Jan，因此直接返回了NULL。而没有返回值。sum()函数相当于是一个遍历的作用。

gruop by 讲的比较好的：https://blog.csdn.net/u014717572/article/details/80687042

题解分析中较好的：https://leetcode-cn.com/problems/reformat-department-table/solution/guan-yu-group-byyu-sumde-pei-he-by-xxiao053/

select id,sum(case when month="Jan" then revenue  else null end) as Jan_Revenue,
sum(case when month="Feb" then revenue else null end) as Feb_Revenue,
sum(case when month="Mar" then revenue else null end) as Mar_Revenue, 
sum(case when month="Apr" then revenue else null end ) as Apr_Revenue,
sum(case when month="May" then revenue else null end) as May_Revenue,
sum(case when month="Jun" then revenue else null end) as Jun_Revenue,
sum(case when month="Jul" then revenue else null end) as Jul_Revenue, 
sum(case when month="Aug" then revenue else null end) as Aug_Revenue,
sum(case when month="Sep" then revenue  else null end) as Sep_Revenue,
sum(case when month="Oct" then revenue else null end) as Oct_Revenue,
sum(case when month="Nov" then revenue else null end) as Nov_Revenue,  
sum(case when month="Dec" then revenue else null end) as Dec_Revenue
from Department 
group by id

【4】620. 有趣的电影

知识点：

Mysql 常见的求余、整除、四舍五入。且支持位运算符。

MOD(id,2)=1
id mod 2 =1  --求余 判断奇数
 
5 div 2  --整除 --python  5//2 等于2

round(1.5)  -- 四舍五入 等于2 

id & 1 -- 位运算 判断奇数

| -- 位或 
& -- 位与
^ -- 位异或
<< -- 位左移运
>> -- 位右移运
& ~ -- 位取反

字符串比较 用 <> ，如果专门判断NULL 利用 is null 和 is not null。

此外，要掌握 <> |  is  not | <>的区别。 https://www.jianshu.com/p/3ee60ef559a0

select id,movie,description,rating
from cinema
where description <> 'boring' and mod(id,2)=1 --id mod 2=1 
order by rating desc;

【5】181. 超过经理收入的员工

摘自@Ratuchetp：”和自己的xxx比”这种问题基本都是自连接问题

select e1.Name as Employee
from  Employee as e1,Employee as e2 
where e1.ManagerId=e2.Id and e1.Salary>e2.Salary 

注意：自连接是where写连接条件，left join等都是on写连接条件。

子查询分为：相关子查询和不相关子查询

不相关子查询：内部查询的执行独立于外部查询，内部查询仅执行一次，执行完毕后将结果作为外部查询的条件使用

相关子查询：内部查询的执行依赖于外部查询的数据，外部查询每执行一次，内部查询也会执行一次。每一次都是外部查询先执行，取出外部查询表中的一个元组，将当前元组中的数据传递给内部查询，然后执行内部查询。根据内部查询执行的结果，判断当前元组是否满足外部查询中的where条件，若满足则当前元组是符合要求的记录，否则不符合要求。然后，外部查询继续取出下一个元组数据，执行上述的操作，直到全部元组均被处理完毕。

参考：https://blog.csdn.net/qiushisoftware/article/details/80874463

select e.Name as Employee
from Employee as e  
where Salary >
 (select Salary
from Employee
where Employee.Id = e.ManagerId)

## 从外部查询中,拿出一个元祖，执行 内部查询,即找见 对应的 经理
## 在判断 salary 和 经理salary 

【6】182. 查找重复的电子邮箱

gruop by 和having使用。having是在分组的基础上进行查询的。

select Email
from Person
group by Email
having count(*)>=2

另外一种解决方法： （在实际生产中，面对千万上亿级别的数据，连接的效率往往最高，因为用到索引的概率较高）

select DISTINCT p1.Email
from Person p1,Person p2
where p1.Email=p2.Email and p1.Id <> p2.Id

【7】196. 删除重复的电子邮箱

UPDATE 和 delete语句，不能嵌套自身的查询。不能先select出同一表中的某些值，再update/delete这个表(在同一语句中)，即不能依据某字段值做判断再来更新某字段的值。

向下面这样不行，会报错类似于：You can't specify target table for update in FROM clause错误。

这种错误只出现于mysql，mssql和oracle不会出现此问题。

delete from Person 
where id not in (
    select min(id) as id 
    from Person  
    group by Email)

解决方法：查询结构作为中间表，再中间表的基础上进行一个查询。

delete from Person 
where id not in (
    select a.id from --从表a中查询,将Person表和查询结果分隔开来
    (select min(id) as id 
    from Person  
    group by Email) as a) --表的别名 a 

解决方法2： 官方给出的。

DELETE p1 FROM Person p1,Person p2 
WHERE p1.Email = p2.Email AND p1.Id > p2.Id

针对这个有个疑问，既然采取了自连接操作，那么为什么还可以删除 person表的内容呢？不应该是删除自连接表的内容吗？

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

这种DELETE方式很陌生，竟然和SELETE的写法类似。它涉及到t1和t2两张表，DELETE t1表示要删除t1的一些记录，具体删哪些，就看WHERE条件，满足就删；这里删的是t1表中，跟t2匹配不上的那些记录。

官方sql中，DELETE p1就表示从p1表中删除满足WHERE条件的记录。参考链接1，参考链接2.

删除的过程中，delete是逐条删除的。原来是一个个拿p1中每个记录去遍历比对，符合条件的就删掉。

【8】183. 从不订购的客户

第一时间想到的就是left join来做。

select Name as Customers
from Customers as c left join Orders as o on c.Id=o.CustomerId 
where o.id is null

exists用法：摘自：https://www.cnblogs.com/byavs/p/11584864.html

https://www.cnblogs.com/xuanhai/p/5810918.html 详细讲解。

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测 行 的存在。

EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT ... FROM...)， 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。

一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

select distinct Name as Customers 
from Customers 
where id not in  (
    select CustomerId
    from orders)

-- don't use 'not in' and 'in' 
-- instead of the 'exists'

select  Name as Customers 
from Customers as c 
where not exists  (       --建立了一个相关子查询操作
    select id
    from orders
    where orders.CustomerId=c.Id)  

提取customers第一条数据，id=1,然后进行相关子查询，由于orders表会返回一个 2，不为空，那么此判断为True。即保留customers中id=1这条数据。再id=2，由于orders表返回空，那么判断为False，则id=2这行不要...以此类推。

                                                                        

【9】175. 组合两个表

由于 不管city和state是否存在，都要返回值。那么内连接是不能用的，因此使用left join。

select p.FirstName, p.LastName, a.City, a.State
from Person as p left join Address as a 
on p.PersonId = a.PersonId  

在巩固一遍 where 和 on。摘自：  https://leetcode-cn.com/problems/combine-two-tables/comments/ @Carl Marx

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用left jion时，on和where条件的区别如下：

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

注意：mysql不支持full outer join，Mysql可以用left join+union+right join来解决。

连接操作如果不清楚，可以利用韦恩图来求解。参见链接。

【10】596. 超过5名学生的课

select a.class  
from (select distinct student,class 
from courses) as a
group by a.class
having count(a.student)>=5;

distinct 可以放在 count()函数中使用。

select class  
from courses
group by class
having count(distinct(student))>=5;

总结这10道题给我的知识点：

（1） if(exp1,epx2,exp3) 和 ifnull(exp1,epx2) 使用方法

（2）case when 使用，不要忘掉结尾的 end

（3）update table_name set column= values where 

（4）delete/update 再where中不能有基于此表对查询操作

（5）相关子查询和不相关子查询区别

（6）列转行问题 ->case when 解决

（7）判断null，利用 is null 或者 is  not null

（8） exists 用法  

（9）delete f1 from person f1,person f2 where ....所使用的删除方法 

（10）去重问题，先想到用distinct 来做。