Leet Code 简单题

[176. 第二高的薪水]

考察范围：子查询；distinct

我的解答：
select (select distinct salary 
        from employee 
        order by salary desc 
        limit 1,1) 
as SecondHighstSalary;
最优解：
select max(salary) as SecondHighstSalary  
from employee
where 
salary <(select max(salary)from employee)

[181. 超过经理收入的员工]

解题思路：先区分人员职位（经理岗or一线岗）
然后比对一线与经理的工资大小
执行顺序：from - where - group by - select -
having(where) - order by3.18

[182. 查找重复的电子邮箱]

知识点考察：group by +having

[183. 从不订购的客户]

知识点： not in 后面的子查询也需要使用select语句！

#自己答案
# Write your MySQL query statement below
select name as customers from customers
where id not in (select customerid from orders);
#top Answer
# Write your MySQL query statement below、
select a.name as Customers from customers a where a.id not in (select distinct b.CustomerId from orders b);
两者区别在于两张表、distinct！

[196. 删除重复的电子邮箱]

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。
知识点考察：

# 官方答案Write your MySQL query statement below
delete p1
from Person p1,Person p2
where p1.Email=p2.Email and p1.Id>p2.Id
#top Answer
# Write your MySQL query statement below
delete from person where id not in 
(select t.id from (select min(id) id from person group by email) t)
#第三种 利用窗口函数 删除排名大于1的！
delete from Person 
where id not in
(select keep_id from
    (select  min(Id) over(partition by Email) as keep_id
from Person) t1 );

3.19

[197. 上升的温度]

编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。
考察内容：自连接;DATEDIFF函数

SELECT w2.Id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w2.RecordDate, w1.RecordDate) = 1
AND w1.Temperature < w2.Temperature

3.22

511 写一条 SQL 查询语句获取每位玩家第一次登陆平台的日期。
解题思路：首先知道是使用group by player_id进行分组
对日期的操作！
grouop by 后面使用 1 2 等可以代表对应的列 ```mysql select player_id,min(event_date) as first_login from activity group by player_id

top answer

select player_id,min(event_date) as first_login from activity group by 1


- **512 请编写一个 SQL 查询，描述每一个玩家首次登陆的设备名称**<br />解题思路：使用最小日期 来对应 设备<br />在where子查询中（player_id，event_date)和后面的select语句中相同
```mysql
select player_id,device_id from activity
where (player_id,event_date) in (select player_id,min(event_date) from activity group by player_id)
#top Answer
使用窗口函数？
排名第一的！
# Write your MySQL query statement below
SELECT 
    player_id
    ,device_id
FROM
    (
    SELECT
        player_id
        ,device_id
        ,row_number() over(PARTITION BY player_id ORDER BY event_date) AS time_rank 
    FROM
        Activity
    ) AS A1
WHERE A1.time_rank = 1

3.23

[577. 员工奖金] bonus < 1000的员工姓名和bonus

解题思路：连接两张表+找出bonus <1000的empid
注意：bonus中未出现的empid他们的bonus是null也符合条件

#我的答案未展现 null值！
select name,bonus from employee e ,bonus b 
where e.empid = b.empid and bonus < 1000 
#展现null值
select name,bonus from employee e left join bonus b 
on e.empid = b.empid where bonus < 1000 or bonus is null
# 使用了 ifnull 函数
select e.name,b.bonus
from Employee e
left join Bonus b
on e.empId=b.EmpId
where ifnull(b.bonus,0)<1000;

[584. 寻找用户推荐人] ！=2

解题思路：!= 2表示数字层
null层需要单独列出。中间使用or来连接

select name from customer 
where referee_id != 2 or referee_id is null
#top Answer
和上述基本一样；!= 改成 <>

3.30

[586.订单最多的客户]

解题思路：分组-按照分组进行排序（从大到小）-取值（limit 1）

# my answer
select customer_number from orders 
group by customer_number order by count(customer_number) desc limit 1
# top answer
Select a.customer_number
From orders as a
Group by a.customer_number
Order by count(*) DESC
Limit 1

[596.超过5名学生选课]

解题思路：group by +having

# 我的错误在于使用了count（class）导致无法过审
select class from courses 
group by class having count(distinct student) >= 5

[597. 好友申请 I：总体通过率]

解题思路：sender_id 与 accept_id 匹配
left join 与right join 的应用
ifnull

select
round(
    ifnull(
    (select count(*) from (select distinct requester_id, accepter_id from request_accepted) as A)
    /
    (select count(*) from (select distinct sender_id, send_to_id from friend_request) as B),
    0)
, 2) as accept_rate;

3.31

[603 连续空余座位]

解题思路：自连接，

# Write your MySQL query statement below
select distinct t1.seat_id
from cinema t1 join cinema t2
on abs(t1.seat_id-t2.seat_id)=1
where t1.free=1 and t2.free=1
order by t1.seat_id;

[607 销售员]

解题思路：Not In 、 outer join
with as ?

# Write your MySQL query statement below
select s.name from salesperson s 
where s.sales_id NOT IN (
    select distinct o.sales_id from orders o join company c on o.com_id = c.com_id and c.name = 'RED'
)

[610 判断三角形]

解题思路：条件判断——case when

使用select  ，case when  注意前面有，
select x, y,z,
case when x+y<=z or x+z<=y or y+z<=x
then 'No'
else 'Yes'
end as triangle
from triangle
# top answer 使用*替代xyz
select *,
case when (x+y>z and x+z>y and y+z>x) then 'Yes'
    else 'No'
    end as triangle
    from triangle

4.3

613 直线上最近的距离

解题思路：自连接 inner join
取绝对值的最小值！——select min(abs())

select min(abs(p1.x - p2.x)) as shortest from point p1 
join point p2 on p1.x <> p2.x;

619 只出现一次最大的数值

解题思路：ifnull or 子查询
注意⚠️：select里的聚合函数是针对每一分组的，不能直接作用于聚合键

# 错误答案
select max(num) as num from my_numbers 
group by num
having count(num) = 1
order by num desc limit 1
# 正确答案-子查询
select max(num) as num from (select  num from my_numbers 
group by num
having count(num) = 1
order by num desc limit 1) as t
# 正确答案-ifnull
select ifnull((select  num from my_numbers 
group by num
having count(num) = 1
order by num desc limit 1),null) as s

620 有趣的电影
627 变更性别

要求只能写update语句，且不产生任何临时表

# Write your MySQL query statement below
UPDATE salary
SET sex = CASE sex WHEN 'f' THEN 'm'
                   ELSE 'f' END
update salary set sex = if(sex = 'f','m','f')
update salary 
set sex=if(
    sex='m','f','m'
)

4.5

1050 至少合作3次的演员和导演

解题思路：group by/ having count()
涉及到两个变量的count 使用 count（*）

select a_id,d_id from a_d
group by a_id,d_id 
having count(*) >=3

1608

解题思路： inner join

4.6

1141 查询近30天的活跃用户数

解题思路：无主键，可能包含重复数据？使用count(distinct user_id)
group by activity_date 后面不跟user_id
对于日期之间差值：30天
between ‘2019-06-28’ and ‘2019-07-27’
或者使用 datediff(‘2019-07-27’,active_date) <30

select active_date as date,count(distinct user_id) as total_user
from activity
where datediff('2019-07-27','active_date')<30
group by active_date

1179 重新格式化部门表行转列（group by case when end )为什么需要+sum函数？https://blog.csdn.net/u014180504/article/details/79150492

解题思路：将其中的列转换成行！

# Write your MySQL query statement below
select id,
    sum(case month when 'Jan' then revenue end) as 'Jan_Revenue',
    sum(case month when 'Feb' then revenue end) as 'Feb_Revenue',
    sum(case month when 'Mar' then revenue end) as 'Mar_Revenue',
    sum(case month when 'Apr' then revenue end) as 'Apr_Revenue',
    sum(case month when 'May' then revenue end) as 'May_Revenue',
    sum(case month when 'Jun' then revenue end) as 'Jun_Revenue',
    sum(case month when 'Jul' then revenue end) as 'Jul_Revenue',
    sum(case month when 'Aug' then revenue end) as 'Aug_Revenue',
    sum(case month when 'Sep' then revenue end) as 'Sep_Revenue',
    sum(case month when 'Oct' then revenue end) as 'Oct_Revenue',
    sum(case month when 'Nov' then revenue end) as 'Nov_Revenue',
    sum(case month when 'Dec' then revenue end) as 'Dec_Revenue'
from Department
group by id

1777题同上
1809 没有广告的时间

解题思路： not in +子查询

select session_id from playback
where session_id not in (
  select seesion_id from playback p left join ads a on p.customer_id = a.customer_id
  where a.timestamp between start_time and endtime
  )

MysqL 学习

0—Easy