黑白灰 网站网络推广网站建设

主要内容

1. LeetCode–高频SQL50题（基础版）1-10

一.SQL练习题

1.1757-可回收且抵制的产品

代码如下（示例）:

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

2.584-寻找用户推荐人

代码如下（示例）:

# Write your MySQL query statement below
select name
from Customer
where referee_id != 2 or referee_id is null;

3.595-大的国家

代码如下（示例）:

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

4.1148-文章浏览

代码如下（示例）:

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

5.1683-无效的推文

代码如下（示例）:

# Write your MySQL query statement below
select tweet_id
from Tweets
where length(content)>15;

6.1378-使用唯一标识码替换员工ID

代码如下（示例）:

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

7.1068-产品销售分析

代码如下（示例）:

# 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.1581-进店却未进行过交易的顾客

代码如下（示例）:

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

9.197-上升的温度

代码如下（示例）:

# Write your MySQL query statement below
select a.id
from weather a inner join weather b 
on a.temperature > b.temperature
and dateDiff(a.recordDate,b.recordDate) = 1;

10.1661-每台机器的进程平均运行时间

代码如下（示例）:

# Write your MySQL query statement below
select a1.machine_id,round(avg(a2.timestamp - a1.timestamp),3) as processing_time
from activity a1,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'
group by a1.machine_id;

---

总结

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