leetcode-高频SQL(基础版)-50题

简单题：无
中等题：💛
困难提：❤️

查询

1757. 可回收且低脂的产品

select product_id
from Products
where low_fats='Y' and recyclable='Y'

584. 寻找用户推荐人

Select name
From customer
Where referee_id!=2 Or referee_id Is null;

595. 大的国家

select name,population,area
from World
where area>=3000000 or population >= 25000000

1148. 文章浏览 I

Select Distinct author_id as id
From views
Where author_id=viewer_id
Order By id;

1683. 无效的推文

Select tweet_id
From tweets
Where length(content)>15;

连接

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

Select e2.unique_id,e1.name
From Employees As e1 Left Outer Join EmployeeUNI As e2
On e1.id=e2.id;

1068. 产品销售分析 I

Select p.product_name,s.year,s.price
From sales As s ,product As p
Where s.product_id = p.product_id

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

Select customer_id,count(*) As count_no_trans
From visits As v left join transactions As t
On t.visit_id = v.visit_id
Where transaction_id is null
Group By customer_id;

197. 上升的温度

Select w1.id
From weather w1 Join weather w2
On Datediff(w1.recordDate,w2.recordDate)=1
Where w1.temperature > w2.temperature

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

select a1.machine_id,round(avg(a2.timestamp-a1.timestamp),3) as processing_time
from activity as a1,activity as 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')
group by a1.machine_id

577. 员工奖金

select e.name,b.bonus
from Employee as e left outer join Bonus as b
on e.empId = b.empId
where b.bonus < 1000 or b.bonus is null;

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

select
  s.student_id,s.student_name,sub.subject_name,ifnull(grouped.attended_exams,0) as attended_exams
from Students s cross join Subjects sub
left join (
  select student_id,subject_name,count(*) as attended_exams
  from Examinations
  group by student_id,subject_name
) grouped
on s.student_id = grouped.student_id and sub.subject_name = grouped.subject_name
order by s.student_id,sub.subject_name

570. 至少有5名直接下属的经理💛

select name
from employee as t1
join (
select managerId
from employee
group by managerId
having count(managerId) >=5) as t2
on t1.id = t2.managerId;

1934. 确认率💛

select s.user_id, round(ifnull(avg(c.action='confirmed'),0),2) as confirmation_rate
from Signups s left join Confirmations c
on s.user_id = c.user_id 
group by user_id

聚合函数

620. 有趣的电影

select id,movie,description,rating
from cinema
where description!='boring' and id%2=1
order by rating desc;

1251. 平均售价

select p.product_id,
	   ifnull(round(sum(p.price*u.units)/sum(u.units),2),0) as average_price
from Prices as p left join UnitsSold as 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

1075. 项目员工 I

select p.project_id,
	   round(sum(e.experience_years)/count(p.project_id),2) average_years
from Project as p,Employee as e
where p.employee_id = e.employee_id
group by p.project_id

1633. 各赛事的用户注册率

select contest_id,
	   round(100*count(r.contest_id)/(select count(u.user_id) from Users u),2) as percentage
from Register as r
group by contest_id
order by percentage desc,contest_id

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

select query_name,
	   round(avg(rating/position),2) quality,
	   round(sum(if(rating<3,1,0))*100/count(*) ,2) poor_query_percentage
from Queries 
group by query_name

1193. 每月交易 I💛

select date_format(trans_date,'%Y-%m') month,
	   country,
	   count(*) trans_count,
	   sum(if(state='approved',1,0)) approved_count,
	   sum(amount) trans_total_amount,
	   sum(if(state='approved',amount,0)) approved_total_amount
from Transactions
group by month,country

1174. 即时食物配送 II💛

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

550. 游戏玩法分析 IV💛

select ifnull(round(count(distinct(Result.player_id))/count(distinct(Activity.player_id)),2),0) as 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.player_id=Expected.player_id and Activity.event_date=Expected.second_date
) as Result,Activity

排序和分组

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

select teacher_id,count(distinct subject_id) cnt
from Teacher
group by teacher_id

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

select activity_date day,
	   count(distinct user_id) active_users
from Activity
where (activity_date between '2019-6-28' and '2019-7-27')
group by activity_date

1084. 销售分析III

select p.product_id,p.product_name
from Product p left join Sales s
on p.product_id=s.product_id 
group by s.product_id
having min(s.sale_date)>='2019-01-01' and max(s.sale_date)<='2019-03-31' 

596. 超过5名学生的课

select class
from Courses
group by class
having count(student)>=5

1729. 求关注者的数量

select user_id,count(*) followers_count
from Followers
group by user_id
order by  user_id

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

select max(num) num
from (select num
from MyNumbers
group by num
having count(num)=1) t

1045. 买下所有产品的客户💛

select customer_id
from 
	(select customer_id,
		   count(distinct product_key) cnt 
	 from customer 
	 group by customer_id) a
where a.cnt = (select count(distinct product_key) from Product)

高级查询和连接

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

select e1.employee_id,
	   e1.name,
	   count(e1.employee_id) reports_count,
	   round(avg(e2.age),0) average_age
from Employees e1,Employees e2
where e1.employee_id = e2.reports_to
group by e1.employee_id
order by e1.employee_id

1789. 员工的直属部门

select employee_id,department_id
from Employee 
where primary_flag='Y'
union
select employee_id,department_id
from Employee 
group by employee_id
having count(employee_id)=1 

610. 判断三角形

select x,y,z,
    case 
    when x+y>z and x+z>y and y+z>x then 'Yes'
    else 'No'
    end as triangle  
from Triangle;

180. 连续出现的数字💛

select distinct l1.num as 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

1164. 指定日期的产品价格💛

select p1.product_id,ifnull(p2.new_price,10) price
from (
    select distinct product_id
    from products
) as p1
left join (
    select product_id,new_price
    from products
    where (product_id,change_date) in (
        select product_id,max(change_date)
        from products
        where change_date <= '2019-08-16'
        group by product_id
    )
) as p2
on p1.product_id = p2.product_id

1204. 最后一个能进入巴士的人💛

select a.person_name
from 
(
  select person_name,@pre := @pre + weight as weight
  from Queue,(select @pre := 0) tmp
  order by turn
) a
where a.weight <= 1000
order by a.weight desc
limit 1

1907. 按分类统计薪水💛

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

子查询

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

select employee_id
from Employees
where manager_id not in (select employee_id from Employees) and salary<30000
order by employee_id

626. 换座位💛

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 

1341. 电影评分💛

(select t1.results
from(select u.name as results,count(u.user_id) ratNum
      from Users u left join MovieRating m2
      on u.user_id = m2.user_id
      group by u.user_id
      order by ratNum desc,results) t1 
      limit 1)
union all
(select t2.results
from(
  select m1.title as results,avg(m2.rating) avgNum
  from Movies m1 left join MovieRating m2
  on m1.movie_id = m2.movie_id
  where date_format(created_at,'%Y-%m')='2020-02' 
  group by m1.movie_id
  order by avgNum desc,results) t2 
  limit 1
)

1321. 餐馆营业额变化增长💛

select visited_on,
sum_amount amount,
round(sum_amount/7,2) average_amount
from
(select visited_on,sum(sum_amount) over (order by to_days(visited_on) range between 6 preceding and current row) sum_amount
from
(select visited_on,sum(amount) as sum_amount
from Customer
group by visited_on) as tmp1) as tmp2
where datediff(visited_on,(select min(visited_on) from Customer)) >= 6;

602. 好友申请 II ：谁有最多的好友💛

select ids as id,cnt as num
from(
select ids,count(ids) as cnt
from(
select requester_id as ids from RequestAccepted
union all
select accepter_id from RequestAccepted) as tmp1
group by ids) as tmp2
order by cnt desc
limit 1

585. 2016年的投资💛

select round(sum(i.TIV_2016),2) as TIV_2016
from insurance i
where i.TIV_2015 in(
  select TIV_2015
  from insurance
  group by TIV_2015
  having count(*) > 1
)
and concat(LAT,LON) in(
  select concat(LAT,LON)
  from insurance
  group by LAT,LON
  having count(*) = 1
)

185. 部门工资前三高的所有员工❤️

select d.name as Department,e1.name as Employee,e1.Salary
from Employee e1 join Department d
on e1.DepartmentId = d.Id
where 3>(
  select count(distinct e2.salary)
  from Employee e2
  where e2.salary > e1.salary and e1.DepartmentId = e2.DepartmentId
)
order by d.name,e1.Salary desc

高级字符串函数 / 正则表达式 / 子句

1667. 修复表中的名字

select user_id,concat(upper(substring(name,1,1)),lower(substring(name,2,length(name)))) as name
from Users
order by user_id

1527. 患某种疾病的患者

select *
from Patients
where conditions REGEXP '\\bDIAB1'

196. 删除重复的电子邮箱

delete p1
from Person p1, Person p2
where p1.email = p2.email and p1.id>p2.id

176. 第二高的薪水💛

select ifnull(
  (select distinct(salary) 
  from Employee
  order by salary desc
  limit 1 offset 1),null) as SecondHighestSalary 

1484. 按日期分组销售产品

select sell_date,
        count(distinct product) as num_sold,
        group_concat(distinct product order by product separator ',') as products
from Activities
group by sell_date
order by sell_date

1327. 列出指定时间段内所有的下单产品

select p.product_name,t2.new_unit unit
from (Products p ,
(select product_id,sum(unit) as new_unit
from Orders
where order_date < '2020-03-01' and order_date >= '2020-02-01'
group by product_id) as t2)
where p.product_id = t2.product_id and t2.new_unit >=100

1517. 查找拥有有效邮箱的用户

select user_id,name,mail
from Users
where mail regexp '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';

sql还有优化的空间，先记录在这❤️