高频SQL50题（基础班）

基础班）"/>

高频SQL50题（基础班）

文章目录

• 主要内容
• 一.SQL练习题
• • • 1.1789-员工的直属部门
    • • • 代码如下（示例）:
    • 2.610-判断三角形
    • • • 代码如下（示例）:
    • 3.180-连续出现的数字
    • • • 代码如下（示例）:
    • 4.1164-指定日期的产品价格
    • • • 代码如下（示例）:
    • 5.1204-最后一个能进入巴士的人
    • • • 代码如下（示例）:
  • 6.1907-按分类统计薪水
  • • • • 代码如下（示例）:
    • 7.1978-上级经理已离职的公司员工
    • • • 代码如下（示例）:
    • 8.626-换座位
    • • • 代码如下（示例）:
    • 9.1341-电影评分
    • • • 代码如下（示例）:
    • 10.1321-餐馆营业额变化增长
    • • • 代码如下（示例）:
• 总结

主要内容

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_datefrom productswhere 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 AccountsUNION
SELECT  'Average Salary' category,SUM(CASE WHEN income >= 20000 AND income <= 50000 THEN 1 ELSE 0 END) AS accounts_count
FROM AccountsUNION
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 idwhen mod(id,2) = 1 then id+1else id-1end)as id,student
from seat
order by id;法2：SELECT(CASEWHEN MOD(id, 2) != 0 AND counts != id THEN id + 1WHEN MOD(id, 2) != 0 AND counts = id THEN idELSE id - 1END) AS id,studentFROMseat,(SELECTCOUNT(*) AS countsFROMseat) AS seat_countsORDER BY id ASC;

9.1341-电影评分

代码如下（示例）:

# Write your MySQL query statement below
(#评论电影数量最多且字典序较小的用户名select users.name as resultsfrom MovieRatingjoin users on MovieRating.user_id = users.user_idgroup by MovieRating.user_idorder by  count(MovieRating.user_id) desc,users.namelimit 1
)
union all (#2020年2月份平均评分最高且字典序较小的电影名select Movies.title as resultsfrom MovieRatingjoin Movies on MovieRating.movie_id = Movies.movie_idwhere MovieRating.created_at >= '2020-02-01'and MovieRating.created_at < '2020-03-01'group by MovieRating.movie_idorder by avg(MovieRating.rating) desc,Movies.titlelimit 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_amountfrom (select visited_on,sum(amount) as amountfrom customergroup by visited_on) TT
) LL
where datediff(visited_on,(select min(visited_on) from customer)) >=6;

总结

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