一道携程SQL笔试题 2020-04-01 23:32 来自一位朋友最近做的携程笔试题，其中有一道关于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的应用名称及其预警数，并按预警数由大到小排序。 原题目图片：  [](https://imgchr.com/i/G86BDI) [](https://imgchr.com/i/G8yXHP)[](https://imgchr.com/i/G86KgJ) 给出的两表数据是这样的：  给出的最终查询结果是这样的： | 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是： ```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应该是： ```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就是： ```sql select app_id, count(*) from alert GROUP BY app_id ``` （上述将`count(*)`换成`count(qpp_id)`是相同的） 查询结果： 2、然后将其按照预警数倒序排序并只拿出top3。sql就是： ```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是： ```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中： ```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正确。（不过我的查询仍不适用于存在相同排名的情况下，只适用本道题目） 希望你看的开心(●'◡'●) --END--
发表评论