高频SQL50题（基础题）-4

主要内容

1. LeetCode-高频SQL50题 31-40

一.SQL练习题

1.1789-员工的直属部门

代码如下（示例）:

# Write your MySQL query statement below
select employee_id,department_id
from employee
where primary_flag = 'Y'
group by 1
union 
select employee_id,department_id
from employee
group by 1
having count(*)=1;

2.610-判断三角形

代码如下（示例）:

# Write your MySQL query statement below
select x,y,z,
    if(x+y+z > 2* greatest(x,y,z),'Yes','No') as triangle
from triangle;

3.180-连续出现的数字

代码如下（示例）:

# Write your MySQL query statement below
select distinct num as ConsecutiveNums
from logs
where (id+1,num) in (select * from logs)
and (id+2,num) in (select * from logs);

4.1164-指定日期的产品价格

代码如下（示例）:

# Write your MySQL query statement below
select product_id,new_price as price 
from products
where (product_id,change_date) in
    (
        select product_id,max(change_date) as max_date
        from products
        where change_date <= '2019-08-16'
        group by product_id
    )
union
select product_id,10 as price
from products
group by product_id
having min(change_date) > '2019-08-16'
order by product_id;

5.1204-最后一个能进入巴士的人

代码如下（示例）:

# Write your MySQL query statement below
select a.person_name
from queue a,queue b 
where a.turn >= b.turn
group by a.person_id
having sum(b.weight) <= 1000
order by a.turn desc
limit 1

6.1907-按分类统计薪水

代码如下（示例）:

# Write your MySQL query statement below
SELECT 
    'Low Salary' AS category,
    SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) AS accounts_count
FROM 
    Accounts
    
UNION
SELECT  
    'Average Salary' category,
    SUM(CASE WHEN income >= 20000 AND income <= 50000 THEN 1 ELSE 0 END) 
    AS accounts_count
FROM 
    Accounts

UNION
SELECT 
    'High Salary' category,
    SUM(CASE WHEN income > 50000 THEN 1 ELSE 0 END) AS accounts_count
FROM 
    Accounts

7.1978-上级经理已离职的公司员工

代码如下（示例）:

# Write your MySQL query statement below
select e1.employee_id
from employees e1 
left join employees e2
on e1.manager_id = e2.employee_id
where e1.manager_id is not null
and e2.employee_id is null
and e1.salary < 30000
order by e1.employee_id;

8.626-换座位

代码如下（示例）:

# Write your MySQL query statement below
select (
    case 
    when mod(id,2) = 1 and id = (select count(*) from seat ) then id
    when mod(id,2) = 1 then id+1
    else id-1
    end
    )as id,
    student
from seat
order by id;

 法2：
 SELECT
     (CASE
         WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
         WHEN MOD(id, 2) != 0 AND counts = id THEN id
         ELSE id - 1
     END) AS id,
     student
 FROM
     seat,
     (SELECT
         COUNT(*) AS counts
     FROM
         seat) AS seat_counts
 ORDER BY id ASC;

9.1341-电影评分

代码如下（示例）:

# Write your MySQL query statement below
(
    #评论电影数量最多且字典序较小的用户名
    select users.name as results
    from MovieRating
    join users on MovieRating.user_id = users.user_id
    group by MovieRating.user_id
    order by  
        count(MovieRating.user_id) desc,
        users.name
    limit 1
)
union all (
    #2020年2月份平均评分最高且字典序较小的电影名
    select Movies.title as results
    from MovieRating
    join Movies on MovieRating.movie_id = Movies.movie_id
    where MovieRating.created_at >= '2020-02-01'
    and MovieRating.created_at < '2020-03-01'
    group by MovieRating.movie_id
    order by 
        avg(MovieRating.rating) desc,
        Movies.title
    limit 1
)

10.1321-餐馆营业额变化增长

代码如下（示例）:

# Write your MySQL query statement below
select distinct visited_on,
    sum_amount as amount,
    round(sum_amount/7,2) as average_amount
from (
    select visited_on,
    sum(amount) over (order by visited_on rows 6 preceding) as sum_amount
    from (
        select visited_on,
            sum(amount) as amount
        from customer
        group by visited_on
    ) TT
) LL
where datediff(visited_on,(select min(visited_on) from customer)) >=6;

---

总结

以上是今天要讲的内容，练习了一些高频SQL题。