Leetcode SQL 50题刷题攻略（上篇）_leetcode怎么刷sql

简介

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

正文开始

1148.文章浏览1

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

1. char_length计算字符串的长度，也即varchar类型的列

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

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

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

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

   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.<字段名>
   1
   2
   3
   4
   5
   6

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

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

   的结果如下：

   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
   1

   的结果如下：

   customer_id	count_no_trans
   30	4

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
   1

   的结果如下：

   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

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

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

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

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

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

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

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

   输入：

   delivery_id	customer_id	order_date	customer_pref_delivery_date
   1	1	2019-08-01	2019-08-02
   2	2	2019-08-02	2019-08-02
   3	1	2019-08-11	2019-08-12
   4	3	2019-08-24	2019-08-24
   5	3	2019-08-21	2019-08-22
   6	2	2019-08-11	2019-08-13
   7	4	2019-08-09	2019-08-09

   输出：

   customer_id	order_date
   1	2019-08-01
   2	2019-08-02
   3	2019-08-21
   4	2019-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

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
2
3

1. max(num)如果没有找到会返回null