力扣高频SQL50题（基础版）

1.可回收且低脂的产品

# Write your MySQL query statement below
select
    product_id
from 
    Products
where
    low_fats = 'Y' and recyclable = 'Y'

2.寻找用户推荐人

# Write your MySQL query statement below
select 
    name
from 
    Customer
where 
    referee_id <> 2 or referee_id is null

3.大的国家

# Write your MySQL query statement below
select
    name,population,area
from
    World
where
    area >= 3000000 or population >= 25000000

4.文章浏览I

# Write your MySQL query statement below
select
    distinct author_id id
from
    Views
where
    author_id = viewer_id
order by
    author_id

5.无效的推文

#对于SQL表，用于计算字符串中字符数的最佳函数是 CHAR_LENGTH(str)，它返回字符串 str 的长度。
另一个常用的函数 LENGTH(str) 在这个问题中也适用，因为列 content 只包含英文字符，没有特殊字符。否则，LENGTH() 可能会返回不同的结果，因为该函数返回字符串 str 的字节数，某些字符包含多于 1 个字节。
以字符 '¥' 为例：CHAR_LENGTH() 返回结果为 1，而 LENGTH() 返回结果为 2，因为该字符串包含 2 个字节。
# Write your MySQL query statement below
select
    tweet_id
from
    Tweets
where
    length(content) > 15

6.使用唯一标识码替换员工ID

# Write your MySQL query statement below
select
    unique_id,name
from 
    Employees e1
left join 
    EmployeeUNI e2
on e1.id = e2.id

7.产品销售分析I

# Write your MySQL query statement below
select
    product_name,year,price
from
    Sales s
left join
    Product p
on s.product_id = p.product_id

8.进店却未进行过交易的顾客

# Write your MySQL query statement below
select
    customer_id,count(customer_id) count_no_trans
from
    Visits v
left join
    Transactions t
on v.visit_id = t.visit_id 
where transaction_id is null
group by customer_id;

9.上升的温度
找出与之前（昨天的）日期相比温度更高的所有的日期id

# Write your MySQL query statement below
select
    w1.id
from
    Weather w1
cross join
    Weather w2
on datediff(w1.recordDate,w2.recordDate ) = 1 and w1.temperature > w2.temperature

10.每台机器进程的平均运行时间

#我的写法
# Write your MySQL query statement below
select a.machine_id,round(avg(b.timestamp-a.timestamp),3) processing_time 
from
    (select
        *
    from
        Activity
    where
        activity_type = 'start') a 
left join
    (select
        *
    from
        Activity
    where
        activity_type = 'end') b
on a.machine_id = b.machine_id 
group by machine_id

别人的写法

# Write your MySQL query statement below
select 
a1.machine_id,
round(avg(a2.timestamp -a1.timestamp ),3) as processing_time 
from  
    Activity as a1 
join 
    Activity as a2 
on 
a1.machine_id=a2.machine_id and 
a1.process_id=a2.process_id and 
a1.activity_type ='start' and a2.activity_type ='end' 
group by machine_id;

11.员工奖金（null值的筛选）

# Write your MySQL query statement below
select
    name,bonus
from
    Employee e
left join
    Bonus b
on e.empId = b.empId
where bonus<1000 or bonus is null

12.学生们参加各科测试的次数

# Write your MySQL query statement below
select 
    a.student_id,a.student_name,a.subject_name,count(if(a.subject_name = e.subject_name, 1, null)) as attended_exams
from 
    (select
        *
    from
        Students s1,Subjects s2) a
left join
    Examinations e
on a.student_id = e.student_id
group by a.student_id,a.student_name,a.subject_name
order by a.student_id,a.subject_name

13.至少有5名直接下属的经理（内连接join）

# Write your MySQL query statement below
select
    name
from
    Employee e
join (select managerId,count(*) cnt from Employee group by managerId having cnt>= 5) a
on e.id= a.managerId

14.确认率

# Write your MySQL query statement below
select s.user_id,round(ifnull(sum(case when action='timeout' then 0 else 1 end)/count(action),0),2) confirmation_rate
from
    Signups s 
left join
    Confirmations c
on s.user_id = c.user_id
group by s.user_id 

15.有趣的电影（取余）

# Write your MySQL query statement below
select *
from cinema
where description  != 'boring' and id%2 != 0
order by rating desc

16.平均售价（null值的考量）

SELECT 
    p.product_id,
    IFNULL(ROUND(SUM(IFNULL(p.price * u.units, 0)) / NULLIF(SUM(u.units), 0), 2), 0) AS average_price
FROM 
    Prices p
LEFT JOIN 
    UnitsSold u 
ON 
    p.product_id = u.product_id
    AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY 
    p.product_id;

17.项目员工I

# Write your MySQL query statement below
select
    project_id,round(avg(experience_years),2) average_years
from Project p 
left join Employee e 
on p.employee_id=e.employee_id
group by project_id

18.各赛事用户注册率（计数以子句的形式出现在计算中）

SELECT 
    r.contest_id,
    ROUND(COUNT(r.user_id) / (SELECT COUNT(*) FROM Users) * 100, 2) AS percentage
FROM 
    Users u
LEFT JOIN 
    Register r 
ON 
    u.user_id = r.user_id
GROUP BY 
    r.contest_id
HAVING 
    r.contest_id IS NOT NULL
ORDER BY 
    percentage DESC, r.contest_id ASC;

19.查询结果的质量和占比

# Write your MySQL query statement below
select query_name,round(avg(rating/position),2) quality,round(sum(if(rating<3,1,0))/count(*)*100,2) poor_query_percentage 
from Queries
group by query_name

20.每月交易I（自表计算，select子句中的别名在 GROUP BY 中不能直接使用，需要使用原始的表达式）

SELECT 
    SUBSTRING(trans_date, 1, 7) AS month,
    country,
    COUNT(state) AS trans_count,
    SUM(IF(state = 'approved', 1, 0)) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM 
    Transactions
GROUP BY 
    SUBSTRING(trans_date, 1, 7), country;

21.即时食物配送（利用布尔值求和计算）

select
    round(sum(order_date=customer_pref_delivery_date)/count(*)*100,2) immediate_percentage 
from
    Delivery
where
    (customer_id,order_date) in (select customer_id,min(order_date) from Delivery group by customer_id)

22.游戏玩法分析 IV(用户连续登录占比问题）

select
    ifnull(round(count(distinct Result.player_id)/count(distinct Activity.player_id),2),0) fraction
from
    (select Activity.player_id as player_id
    from (
        select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
        from Activity
        group by player_id
    ) as Expected, Activity
    where Activity.event_date = Expected.second_date and Activity.player_id = Expected.player_id) Result,Activity

23.每位教师所教授的科目的种类的数量

# Write your MySQL query statement below
select teacher_id,count(distinct subject_id) cnt 
from Teacher
group by teacher_id

24.最近30天活跃用户数（日期区间的比较应该把小的日期放在前面）

# Write your MySQL query statement below
select activity_date day,count(distinct user_id) active_users 
from Activity
where activity_date between '2019-06-28' and '2019-07-27' 
group by activity_date

25.销售分析（商品仅在某区间销售可使用最大日期最小日期来筛选）

# Write your MySQL query statement below
select p.product_id,product_name
from Sales s
join Product p
on p.product_id  = s.product_id 
group by s.product_id
having max(sale_date)<='2019-03-31' and min(sale_date)>='2019-01-01'

26.超过五名学生的课

# Write your MySQL query statement below
select
    class
from
    (select
        class,count(student)
    from
        Courses
    group by
        class
    having count(student)>=5) a

27.求关注者的数量

# Write your MySQL query statement below
select
    user_id,count(follower_id) followers_count
from
    Followers
group by
    user_id
order by user_id

28.求只出现一次的最大数字

# Write your MySQL query statement below
select if(max(num),max(num),null) num
from    
    (select num,count(num) cnt
    from MyNumbers
    group by num
    having count(num)=1) a

29.买下所有产品的客户

# Write your MySQL query statement below
select customer_id
from Product p
left join Customer c
on p.product_key  = c.product_key
group by customer_id
having count(distinct c.product_key)=(SELECT COUNT(DISTINCT product_key) FROM Product)

30.每位经理的下属员工数量

# Write your MySQL query statement below
select employee_id,name,reports_count,average_age 
from Employees e
join 
    (select reports_to,count(reports_to) reports_count,round(avg(age),0) average_age 
    from Employees
    group by reports_to) a
on e.employee_id = a.reports_to
order by employee_id 

31.员工的直属部门

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

32.判断三角形(case when关键词)

# Write your MySQL query statement below
select x,y,z,case when x>=y and x+y>z and x-y<z then 'Yes' when x<y and x+y>z and y-x<z then 'Yes' else 'No' end as triangle
from Triangle 

33.连续出现的数字（用where和distinct来解决连续出现的问题）

# Write your MySQL query statement below
select distinct l1.num ConsecutiveNums
from Logs l1,Logs l2,Logs l3
where l1.id=l2.id-1 and l2.id = l3.id-1 and l1.num = l2.num and l2.num = l3.num

34.指定日期的产品价格

# Write your MySQL query statement below
select p1.product_id,ifnull(p2.new_price,10) price
from (select distinct product_id
    from Products) p1
left join
    (select product_id,new_price
    from Products
    where (product_id,change_date) in
        (select product_id, max(change_date) change_date
        from Products
        where change_date <= '2019-08-16'
        group by product_id)) p2
on p1.product_id = p2.product_id

35.最后一个能进巴士的人

SELECT person_name
FROM Queue
WHERE turn = (
    SELECT MAX(turn)
    FROM (
        SELECT 
            turn,
            SUM(weight) OVER (ORDER BY turn) AS cumulative_sum
        FROM 
            Queue
    ) subquery
    WHERE cumulative_sum <= 1000
);

36.按分类统计薪水（用case when添加新字段并赋值）

select 
    'Low Salary' category,
    sum(case when income < 20000 then 1 else 0 end) accounts_count
from Accounts
union
select 
    'High Salary' category,
    sum(case when income > 50000 then 1 else 0 end) accounts_count
from Accounts
union
select 
    'Average Salary' category,
    sum(case when income >= 20000 and income <= 50000 then 1 else 0 end) accounts_count
from Accounts

37.上级经理已离职的公司员工

# Write your MySQL query statement below
select
    employee_id
from
    Employees
where salary < 30000 and manager_id not in (select employee_id from Employees)
order by employee_id 

38.电影评分（行转列，列转行）

select name as results
from
(select m1.user_id,name,count(distinct m1.movie_id) cnt
from MovieRating m1
left join Movies m2
on m1.movie_id = m2.movie_id
left join Users u
on m1.user_id = u.user_id
group by m1.user_id
order by cnt desc,name
limit 1) b
union all
select title as results
from
(select m1.movie_id,title,avg(rating) point
from MovieRating m1
left join Movies m2
on m1.movie_id = m2.movie_id
where created_at like "2020-02%"
group by m1.movie_id,title
order by point desc,title
limit 1) a

39.好友申请：谁有最多的好友
select id,count(id) num from (select requester_id id from RequestAccepted union all select accepter_id id from RequestAccepted) a group by id order by num desc limit 1

40.部门工资前三高的所有员工（排序开窗函数dense_rank）

select Department,Employee,salary
from 
    (select d.name Department,e.name Employee,salary,dense_rank()over(partition by d.id order by salary desc) rk
    from Employee e 
    left join Department d
    on e.departmentId = d.id) a
where rk <= 3

41.修复表中的名字（大小写转换）
select user_id,concat(UPPER(substring(name,1,1)),lower(substring(name,2)))name from Users order by user_id

42.患某种疾病的患者
select * from Patients where conditions like "DIAB1%" or conditions like "% DIAB1%"

43.第二高的薪水（ifnull的使用）
select ifnull((select distinct salary from Employee order by salary desc limit 1,1),Null) SecondHighestSalary

44.按日期分组销售产品（group_concat)
select sell_date,count(distinct product) num_sold,group_concat(distinct product order by product separator ',') products from Activities group by sell_date order by sell_date asc

45.列出指定时间段内所有的下单产品
select product_name,sum(unit) unit from Products p left join Orders o on p.product_id=o.product_id where order_date like "2020-02%" group by product_name having sum(unit) >= 100

46.查找拥有有效邮箱的用户（正则表达式）
SELECT user_id, name, mail FROM Users WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$'

47.删除重复的电子邮箱
delete from Person where id not in ( select id from(select min(id) id, email from Person group by email ) a)