Leetcode SQL 50题刷题攻略（上篇）

高频 SQL 50 题（基础版）刷题攻略（上篇）

简介

刷题网址：高频 SQL 50 题（基础版） - 学习计划 - 力扣（LeetCode）全球极客挚爱的技术成长平台
本次通过刷完50题，我总结了刷题过程遇到的难题的知识点。
如果你最近准备刷SQL50题，而对于Mysql不熟悉，那么太适合看这个blog了！
下篇网址：Leetcode SQL 50题刷题攻略（下篇）-CSDN博客

正文开始

1148.文章浏览1

image-20231116103952238

image-20231116103924267

  1. order by 列名 asc是按列名升序排列

    order by <列名1> desc, <列名2> asc

  2. author_id as id是将author_id以id列名返回

  3. distinct是去重操作，返回的id是没有重复的值

1683.无效的推文

select tweet_id from tweets where CHAR_LENGTH(content)>15
  1. char_length计算字符串的长度，也即varchar类型的列

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

select unique_id, name from Employees left join EmployeeUNI on Employees.id = EmployeeUNI.id

  1. sql语法中的连接问题，有如下格式

    FROM first_table < join_type > second_table [ ON ( join_condition ) ]

    left join必须有on，join可以没有on

    join_type有6种：

    • left join
    • right join
    • full join
    • inner join
    • union all
    • union

    SQL 表连接_sql连接两张表-CSDN博客

    MySQL中不支持 FULL JOIN，实际业务中，基本不用全连接。

  2. 多表连接语法举例：

    SELECT <字段名> 
FROM <表a> 
LEFT JOIN <表b> 
ON a.<字段名> = b.<字段名>
LEFT JOIN <表C> 
ON a.<字段名> = c.<字段名>

1581.进店却从未进行过消费的顾客

select customer_id,count(customer_id) as count_no_trans from Visits v left join Transactions t on v.visit_id = t.visit_id where t.visit_id is null group by customer_id

  1. select customer_id from Visits v left join Transactions t on v.visit_id = t.visit_id where t.visit_id is null

    的结果如下：

    count_no_trans
    30
    96
    54
    54
  2. select customer_id,count(customer_id) as count_no_trans from Visits v left join Transactions t on v.visit_id = t.visit_id where t.visit_id is null

    的结果如下：

    customer_idcount_no_trans
    304
  3. select customer_id from Visits v left join Transactions t on v.visit_id = t.visit_id where t.visit_id is null group by customer_id

    的结果如下：

    customer_id
    30
    96
    54

  4. 所以要计算某一个列的所有不同元素的值的分别的个数要使用count(<那一列>)加上group by <那一列>

197.上升的温度

select w2.id as id from Weather w1 inner join Weather w2 where w1.recordDate=date_sub(w2.recordDate,interval 1 day) and w2.temperature>w1.temperature
  1. 日期类型date使用date_sub与date_add来添加一天等，即此处的date_sub(w2.recordDate,interval 1 day)

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

select machine_id,round(sum(timestamp)/count(machine_id),3) as processing_time from (select a1.machine_id as machine_id,a2.timestamp-a1.timestamp as timestamp from Activity a1 join Activity a2 where a1.machine_id = a2.machine_id and a1.process_id = a2.process_id and a1.activity_type='start' and a2.activity_type='end') tmp group by machine_id

  1. 使用round来保留小数位数，round(sum(timestamp)/count(machine_id),3)保留3位小数

  2. 使用group by与sum搭配使用与1581的count搭配效果一样

    一般group by使用主键，这样可以避免出现两个不同主键分到一组

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

select s.student_id,s.student_name,su.subject_name,count(e.subject_name) as attended_exams from Students s join Subjects su left join Examinations e on s.student_id = e.student_id and su.subject_name = e.subject_name group by su.subject_name,s.student_name order by s.student_id,su.subject_name
  1. group by可以针对多个列来进行group by su.subject_name,s.student_name

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

select query_name,round(sum(rating/position)/count(query_name),2) as quality,round(count(rating<3 or null)/count(query_name)*100,2) as poor_query_percentage from Queries group by query_name

  1. count(条件表达式)

    • count(case when(条件) then 列名 end)

    • count(表达式 or null) ，满足条件的计算数目，其余不计算

    • count(if(is_reply=1,1,NULL))，满足条件的计算数目，其余不计算

      count(if(is_reply=1,1,0))这种不管满不满足条件都会计算数目

1193.每月交易1

select DATE_FORMAT(trans_date,"%Y-%m") as month,country,count(state) as trans_count,count(state='approved' or null) as approved_count,sum(amount) as trans_total_amount,sum(if(state='approved',amount,0)) as approved_total_amount from Transactions group by DATE_FORMAT(trans_date,"%Y年%m月"),country

  1. group by DATE_FORMAT(trans_date,"%Y年%m月"),country

    按日期的月份以及国家来分组。如果要按日期的年份那么只需要保留%Y即可

  2. sum(…)也可以使用条件表达式

1174.即时食物配送2

select round(count(if(t.order_date=d.customer_pref_delivery_date,1,null))/count(t.order_date)*100,2) as immediate_percentage from (select customer_id,MIN(order_date) as order_date from Delivery d group by customer_id) t left join Delivery d on t.order_date=d.order_date and t.customer_id=d.customer_id

  1. 如何分组选出最小日期

    select customer_id,MIN(order_date) as order_date from Delivery d group by customer_id

    输入：

    delivery_idcustomer_idorder_datecustomer_pref_delivery_date
    112019-08-012019-08-02
    222019-08-022019-08-02
    312019-08-112019-08-12
    432019-08-242019-08-24
    532019-08-212019-08-22
    622019-08-112019-08-13
    742019-08-092019-08-09

    输出：

    customer_idorder_date
    12019-08-01
    22019-08-02
    32019-08-21
    42019-08-09

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

select activity_date as day,count(distinct user_id) as active_users from Activity where activity_date<='2019-07-27' and activity_date>'2019-06-27' group by activity_date
  1. 比较日期的大小，activity_date是date类型，使用activity_date<='2019-07-27' and activity_date>'2019-06-27'的方式来对比大小

619.只出现一次的最大数字

select max(num) as num from
(select num,count(num) as num_n from MyNumbers group by num) t
where num_n=1
  1. max(num)如果没有找到会返回null