This is defy_odd's blog

人在有闲的时候，才最像一个人 —— 梁实秋

sql急救

发表于 更新于 分类于
本文字数： 4k 阅读时长 ≈ 4 分钟

虽然最后没用上.jpg

#7 分数排名

编写一个 sql 查询来实现分数排名。

如果两个分数相同，则两个分数排名（rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

1
2
3
4
5
6
7
8
9
10
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

例如，根据上述给定的 scores 表，你的查询应该返回（按分数从高到低排列）：

1
2
3
4
5
6
7
8
9
10
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

重要提示：对于 mysql 解决方案，如果要转义用作列名的保留字，可以在 关键字之前和 之后使用撇号。例如 rank

题解

最后的结果包含两个部分，第一部分是 降序排列的分数，第二部分是每个分数对应的排名。

第一部分不难写：

1
select a.score as scorefrom scores aorder by a.score desc

比较难的是第二部分。假设现在给你一个分数x，如何算出它的排名rank呢？
我们可以先提取出大于等于x的 所有分数集 合h，将h去重后的元素个数就是x的排名。比如你考了99分，但最高的就只有99分，那么去重之后集合h里就只有99一个元素 ，个数为1， 因此你的rank为1。
先提取集合h：

1
select b.score from scores b where b.score >= x;

我们要的是集合h去重之后 的元素个数，因此升级为：

1
select count(distinct b.score) from scores b where b.score >= x as rank;

而从结果的角度来看，第二部分的rank是对应第一部分的分数来的，所以这里的x就是上面的a.score，把两部分结合在一起为：

1
2
3
4
5
6
7
8
select a.score as score,
(
    select count(DISTINCT b.score)
    from scores b
    where  b.score>=a.score 
) as 'rank'
from scores a 
order by a.score desc

#10 连续出现的数字

表：logss

1
2
3
4
5
6
+-------------+---------+
| column name | type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+

id 是这个表的主键。

编写一个 sql 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

查询结果格式如下面的例子所示：

logss 表：

1
2
3
4
5
6
7
8
9
10
11
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

result 表：

1
2
3
4
5
+-----------------+
| consecutivenums |
+-----------------+
| 1               |
+-----------------+

1 是唯一连续出现至少三次的数字。

题解

1
2
3
4
select DISTINCT l1.num as consecutivenums
from logss l1,logss l2,logss l3
where l1.id=l2.id-1 and l1.id=l3.id-2 
and l1.num=l2.num and l1.num=l3.num;

#11 两人之间的通话次数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
表： calls
+-------------+---------+
| column name | type    |
+-------------+---------+
| from_id     | int     |
| to_id       | int     |
| duration    | int     |
+-------------+---------+
该表没有主键，可能存在重复项。
该表包 含 from_id 与 to_id 间的一次电话的时长。from_id != to_id

编写 sql 语句，查询每一对用户 (person1, person2) 之间的通话次数和通话总时长，其中 person1 < person2 。
以任意顺序返回结果表。查询结果格式如下示例所示：
calls 表：
+---------+-------+----------+
| from_id | to_id | duration |
+---------+-------+----------+
| 1       | 2     | 59       |
| 2       | 1     | 11       |
| 1       | 3     | 20       |
| 3       | 4     | 100      |
| 3       | 4     | 200      |
| 3       | 4     | 200      |
| 4       | 3     | 499      |
+---------+-------+----------+
结果表 ：
+---------+---------+------------+----------------+
| person1 | person2 | call_count | total_duration |
+---------+---------+------------+----------------+
| 1       | 2       | 2          | 70             |
| 1       | 3       | 1          | 20             |
| 3       | 4       | 4          | 999            |
+---------+---------+------------+----------------+
用户 1 和 2 打过 2 次电话，总时长为 70 (59 + 11)。
用户 1 和 3 打过 1 次电话，总时长为 20。
用户 3 和 4 打过 4 次电话，总时长为 999 (100 + 200 + 200 + 499)。

题解：if

1
2
3
4
5
6
7
8
select tmp.t1 person1,tmp.t2 person2,count(*) call_count,sum(duration) total_duration
from
(
    select if(from_id>to_id,to_id,from_id) t1,
           if(from_id>to_id,from_id,to_id) t2,
    	   duration from calls
) as tmp
group by tmp.t1,tmp.t2;

#39 查找拥有有效邮箱的用户

邮箱表： email

1
+---------------+---------+| column name   | type    |+---------------+---------+| user_id       | int     || name          | varchar || mail          | varchar | +---------------+---------+user_id （用户 id）是该表的主键。这个表包含用户在某网站上注册的信息。有些邮箱是无效的。

写一条 sql 语句，查询拥有有效邮箱的用户。

有效的邮箱包含符合下列条件的前缀名和域名：

1
前缀名是包含字母（大写或小写）、数字、下划线 '_'、句点 '.' 和/或横杠 '-' 的字符串。前缀名必须以字母开头。   域名是 '@leetcode.com' 。

按 任意顺序返回结果表。

查询格式如下所示：email

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
+---------+-----------+-------------------------+
| user_id | name      | mail                    |
+---------+-----------+-------------------------+
| 1       | winston   | winston@leetcode.com    |
| 2       | jonathan  | jonathanisgreat         |
| 3       | annabelle | bella-@leetcode.com     |
| 4       | sally     | sally.come@leetcode.com |
| 5       | marwan    | quarz#2020@leetcode.com |
| 6       | david     | david69@gmail.com       |
| 7       | shapiro   | .shapo@leetcode.com     |
+---------+-----------+-------------------------+
结果表：
+---------+-----------+-------------------------+
| user_id | name      | mail                    |
+---------+-----------+-------------------------+
| 1       | winston   | winston@leetcode.com    |
| 3       | annabelle | bella-@leetcode.com     |
| 4       | sally     | sally.come@leetcode.com |
+---------+-----------+-------------------------+
2 号用户的邮箱没有域名。
5 号用户的邮箱包含非法字符 #。
6 号用户的邮箱的域名不是 leetcode。
7 号用户的邮箱以 句点（.）开头。

题解：regexp

1
^ 表示以后面的字符为开头  [] 表示括号内任意字符

  • 表示连续

    • 表示重复前面任意字符任意次数

      \ 用来转义后面的特殊字符，以表示字符原本的样子，而不是将其作为特殊字符使用
      $ 表示以前面的字符为结尾

1.前缀名以字母开头：^[a-zA-Z]
2.前缀名包含字母（大写或小写）、数字、下划线、句点. 和 或 横杠-：[a-zA-Z0-9\.-]*

1
2
3
select * 
from email
where mail regexp '^[a-zA-Z][a-zA-Z0-9\_\.\-]*@leetcode.com$';