一道携程SQL笔试题

以下内容首发于我的个人博客网站：
http://riun.xyz

---

来自一位朋友最近做的携程笔试题，其中有一道关于SQL的题目是这样的：

一、题目

Mysql数据库中有如下两张表，app表为应用表，储存的是应用相关信息；alert表为预警表，储存的是各应用在一段时间内的预警信息。

app：

app_id	app_name
1001	应用1
1002	应用2
…	…

alert：

alert_id	app_id	alert_info	alert_date
324324	1001	预警信息1	2020-02-01 12:30:23
342343	1002	预警信息2	2020-02-01 12:31:23
…	…	…	…

请写出查询出前7天内预警数是Top3的应用名称及其预警数，并按预警数由大到小排序。

原题目图片：

给出的两表数据是这样的：

给出的最终查询结果是这样的：

app_id	app_name	alert_count
1001	application1	4
1005	application5	3
1002	application2	2

二、我的理解

最初审视这道题目的时候，读到**”查询出前7天内预警数是Top3”，我的想法是从最早的时间开始，向后算7天内的数据**。即，若今天是4.1号，最早的时间就是3.22号，那么前7天内的数据应该是在3.22~3.28之间的数据。我们暂且把这个叫做思路1。

按照思路1，我开始写sql，但最终因为无法想出【从最早的时间开始，向后数7天】这个限制条件到底应该怎样写而以失败告终。所以只写了一个不包含此条件的sql。

我的sql是：

select app.app_id, app.app_name, res.count alert_count
from app RIGHT JOIN 
(select app_id, count(*) count from alert GROUP BY app_id order by count desc LIMIT 3) res 
on app.app_id = res.app_id

如果知道这个条件怎样写，那么向里面添加 where 时间限制条件即可。

即，最终的sql应该是：

select app.app_id, app.app_name, res.count alert_count
from app RIGHT JOIN 
(select app_id, count(*) count from alert where 时间限制条件 GROUP BY app_id order by count desc LIMIT 3) res 
on app.app_id = res.app_id

暂且先不说这个时间限制条件，也不说这个sql是否符合题意要求，来说说我的思路吧。

题目：【查询出前7天内预警数是Top3的应用名称及其预警数，并按预警数由大到小排序】，我的思路是如下展开的：

1、由于在alert表中每条同一台应用的预警信息都有相同的app_id，那么我首先想到了要在alert表里对app_id进行分组，分组后查出各组的数量，就得到了应用与预警数的对照表。那么sql就是：

select app_id, count(*) from alert GROUP BY app_id

（上述将count(*)换成count(qpp_id)是相同的）

查询结果：

2、然后将其按照预警数倒序排序并只拿出top3。sql就是：

select app_id, count(*) count from alert GROUP BY app_id ORDER BY count desc LIMIT 3

查询结果：

这样只需要再拿到app_name即可。app_name在app表里，那么将查询结果作为一个新表与app表做连接查询即可查询出结果。sql是：

select app.app_id, app.app_name, res.count alert_count
from app RIGHT JOIN 
(select app_id, count(*) count from alert GROUP BY app_id order by count desc LIMIT 3) res 
on app.app_id = res.app_id

查询结果：

这样看来好像添加上 where 时间限制条件这道题目就解决了。可是后来我又重新审视了一遍题目，发现了一些问题…

三、另一个选择

当我再次审视这道题时，我将数据仔仔细细的对照了一遍。如果按照我的思路1，即，题目中的【查询出前7天内预警数是Top3】这样理解：“若今天是4.1号，最早的时间就是3.22号，那么前7天内的数据应该是在3.22~3.28之间的数据“。仔细观察给出的数据，如果这样算，所有数据都被包含进去了，并没有任何一个数据因为【前7天内】这个限制条件而被刷掉，那么给出的查询限制条件就没有了意义。那么干嘛还要这个多余的限制条件呢？为了迷惑作答者的思路？为了让作答者写这个”比较难写而对本题无意义的“的时间限制条件吗？

我觉得不是。

于是我就尝试着换了一个我并不赞同的方式思考。假设题目中【查询出前7天内预警数是Top3】是另一个意思：以目前为时间点，向前数7天。比如今天是4.1，那么前7天内的数据应该是在3.26~4.1之间的数据。 再回头看数据， 这样算来就存在3.22号和3.24号这两天的数据被刷掉，那么这个限制条件便有了意义。我们把这个叫做思路2。

现在我们不写sql语句，我口述下数据情况（顺序不代表插入顺序）来判断此思路是否正确：

• 3.22号1003机器预警
• 3.14号1002机器预警
• 3.26号：
  • 1001机器预警，共4次
  • 1002机器预警，共2次
  • 1003机器预警，共1次
  • 1004机器预警，共1次
  • 1005机器预警，共3次

以上是给出的数据所表达出来的是实际情况。而题目**“请写出查询出前7天内预警数是Top3的应用名称及其预警数，并按预警数由大到小排序。”** 所给出的结果是：

top3分别是：

• 1001，4次预警
• 1005，3次预警
• 1002，2次预警

就说明了题目是按照思路2来的。

如果按照思路1来，没有数据被刷掉，使用了全部数据，那么结果应该是：

top3分别是：

• 1001，4次预警
• 1002、1005，均3次预警
• 1003，2次预警

显然并不符合给出的结果。

好了，这个乌龙被发现后，我再次看我按照思路1写的sql：就算我们找到了那个符合思路1的正确的where 时间限制条件，添加进去我们的sql仍然不对，因为题目她并不是这个意思啊 …( ＿ ＿)ノ｜。 不知道这个算不算是出题者的一个失误：按照题目的意思明显是思路1，但是给出的结果却是参照思路2的。

除此之外我的sql还有一个错误，按照思路1来top3包含了4条数据，有一个同排名的数据。而我并没有查出4条数据，且至今仍不知道如何将这个相同排名的全部查出来…

【相同排名全部查询】先告一段落，我们来看这道题。现在知道了她是按照思路2走，就好办了，以当前时间点为参照，查询出前7天内的数据的限制语句是：

where DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(时间字段)

我们将此字段添加到我的sql中：

select app.app_id, app.app_name, res.count alert_count
from app RIGHT JOIN 
(select app_id, count(*) count from alert where DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= 
date(alert_date) GROUP BY app_id order by count desc LIMIT 3) res 
on app.app_id = res.app_id

查询结果：

和所要求的查询结果一致。sql正确。（不过我的查询仍不适用于存在相同排名的情况下，只适用本道题目）

希望你看的开心(●’◡’●)