【SQL每日一练】day22：常考高频习题

题目一：平面上的最近距离

题目要求：

p1(x1, y1) 和 p2(x2, y2) 这两点之间的距离是 sqrt((x2 - x1)2 + (y2 - y1)2) 。

编写解决方案，报告 Point2D 表中任意两点之间的最短距离。保留 2 位小数 。

表结构：

运行结果示例：

思路：

        自连接计算距离值，距离值的计算方法参考题目中给出的公式，使用power（）函数计算平方值。其中我们可以通过增加限定条件减少计算的次数，从而提高执行速度。

运行代码示例：

select round(min(sqrt(power(p1.x-p2.x, 2)+power(p1.y-p2.y, 2))), 2) shortest
from point_2d p1 
join point_2d p2 
on p1.x<p2.x or (p1.x=p2.x and p1.y<p2.y)

题目二：统计各专业学生人数

题目要求：

编写解决方案，为 Department 表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。

按 student_number 降序 返回结果表。如果是平局，则按 dept_name 的  字母顺序 排序。

表结构：

运行结果示例：

思路：

        将专业表作为主表与学生表连接，按照专业名称分组，统计每组的数量。

运行代码示例：

select dept_name,count(student_id) student_number
from student s right join department d on s.dept_id = d.dept_id
group by dept_name
ORDER BY student_number DESC , d.dept_name

题目三：英超积分榜排名

题目要求：

编写一个解决方案来计算联盟中每支球队的 得分 和 排名。积分计算方式如下：

• 赢局 有 3 点得分
• 平局 有 1 点得分
• 输局 有 0 点得分

注意：积分相同的球队必须分配相同的排名。

返回结果表以 points 降序 排序，然后以 team_name 升序 排序。

表结构：

运行结果示例：

思路：

        首先计算出每支队伍的得分作为临时表，再在该临时表中使用RANK() OVER()函数进行排名。

运行代码示例：

WITH TeamPoints AS (
    SELECT
        team_id,
        team_name,
        3 * wins + draws as points
    FROM TeamStats
)
SELECT
    team_id,
    team_name,
    points,
    RANK() OVER (ORDER BY points DESC) AS position
FROM TeamPoints
ORDER BY points DESC, team_name ASC

题目四：查找州的每个城市

题目要求：

编写一个解决方案来 查找每个州的所有城市，并将它们组合成 一个逗号分隔 的字符串。

返回结果表以 state 升序 排序。

表结构：

运行结果示例：

思路：

        使用group by 按照各个州进行分组，再使用group_concat（）函数进行拼接。group_concat第一个参数是指根据xx字段拼接，第二个参数根据xx字段排序，第三个参数指明拼接的separator

运行代码示例：

select state,group_concat(city order by city separator ', ')  as cities
from cities
group by state
order by state

题目五：第二天验证

题目要求：

编写一个解决方案来找到 第二天验证注册 的用户 ID。

返回结果表以 user_id 升序 排序。

表结构：

运行结果示例：

思路：

        将两张表进行连接，查找验证状态为“已验证”且注册日期与验证日期相差为1的用户id。最后对查询结果按照指定字段进行排序。

运行代码示例：

select user_id from emails e 
join texts t 
using(email_id)
where datediff(signup_date, action_date) = -1 and signup_action = 'Verified'
order by user_id

题目六：无效的推文

题目要求：

编写一个解决方案来找到 无效的推文。如果一条推文满足下面 任一 条件会被认为无效：

• 长度超过 140 个字符。
• 有超过 3 次提及。
• 有超过 3 个标签。

以 tweet_id 升序 返回结果表。

表结构：

运行结果示例：

思路：

        按照题目要求将条件转换为代码即可。字符串长度可以通过length（）进行获取，包含提及和标签可以用字符串替换实现，将推文内容中的#和@符号用空格进行替换，再统计字符数量，如果数量与原始数量差值大于3表示#和@出现次数多于三次。也可以直接用模糊匹配进行实现。

运行代码示例：

select tweet_id 
from Tweets
where length(content)>140 or 
    (length(content)-length(replace(content,'#','')))>3 or
    (length(content)-length(replace(content,'@','')))>3 
order by tweet_id

select tweet_id
from Tweets
where length(content) > 140 or content like '%#%#%#%#%' or content like '%@%@%@%@%'
order by tweet_id