【高频SQL基础50题】21-25

我又回来了。

目录

1.删除重复的电子邮箱

2.文章浏览 I

3.上升的温度 

4.各赛事的用户注册率 

5.超过 5 名学生的课 

1.删除重复的电子邮箱

 

高级字符串检索题。

使用自连接的方式，面对上亿级别数据，效率也可以很高。

# Write your MySQL query statement below
DELETE p1 FROM Person p1, Person p2
WHERE p1.Email=p2.Email AND p1.Id>p2.Id

2.文章浏览 I

 

查询题。

 

# Write your MySQL query statement below
SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id=viewer_id
ORDER BY id

3.上升的温度 

 

连接题。参考佬解

交叉连接

 

交叉连接，选出数据，输出数据。

 

  

# Write your MySQL query statement below
SELECT a.id
FROM weather AS a CROSS JOIN weather AS b ON DATEDIFF(a.recordDate,b.recordDate)=1
WHERE a.Temperature>b.Temperature;

 

4.各赛事的用户注册率 

 

聚合函数题。

# Write your MySQL query statement below
SELECT contest_id,ROUND(COUNT(user_id)*100/(SELECT COUNT(*) FROM users),2) percentage
FROM Register
GROUP BY contest_id
ORDER BY percentage DESC,contest_id

 

5.超过 5 名学生的课 

 排序和分组题。

解决：分组+count

# Write your MySQL query statement below
SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(*)>4;