一道携程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的应用名称及其预警数,并按预警数由大到小排序。 原题目图片: ![G8yyp4.jpg](https://s1.ax1x.com/2020/04/01/G8yyp4.jpg) [![G86BDI.md.jpg](https://s1.ax1x.com/2020/04/01/G86BDI.md.jpg)](https://imgchr.com/i/G86BDI) [![G8yXHP.md.jpg](https://s1.ax1x.com/2020/04/01/G8yXHP.md.jpg)](https://imgchr.com/i/G8yXHP)[![![G86KgJ.md.jpg](https://s1.ax1x.com/2020/04/01/G86KgJ.md.jpg)](https://imgchr.com/i/G86KgJ) 给出的两表数据是这样的: ![G8DOBV.jpg](https://s1.ax1x.com/2020/04/01/G8DOBV.jpg)![G8rAHK.jpg](https://s1.ax1x.com/2020/04/01/G8rAHK.jpg) 给出的最终查询结果是这样的: | 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 ``` ![G827IP.jpg](https://s1.ax1x.com/2020/04/01/G827IP.jpg) 如果知道这个条件怎样写,那么向里面添加 `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)`是相同的) 查询结果:![G8Ww7R.jpg](https://s1.ax1x.com/2020/04/01/G8Ww7R.jpg) 2、然后将其按照预警数倒序排序并只拿出top3。sql就是: ```sql select app_id, count(*) count from alert GROUP BY app_id ORDER BY count desc LIMIT 3 ``` 查询结果:![G8W4AI.jpg](https://s1.ax1x.com/2020/04/01/G8W4AI.jpg) 这样只需要再拿到`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 ``` 查询结果:![G8fAb9.jpg](https://s1.ax1x.com/2020/04/01/G8fAb9.jpg) 这样看来好像添加上 `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 ``` 查询结果:![G84XHH.jpg](https://s1.ax1x.com/2020/04/01/G84XHH.jpg) 和所要求的查询结果一致。sql正确。(不过我的查询仍不适用于存在相同排名的情况下,只适用本道题目) 希望你看的开心(●'◡'●) --END--
发表评论