一道简单的sql语句题

最新推荐文章于 2023-05-09 10:32:45 发布

LeadAI学院

最新推荐文章于 2023-05-09 10:32:45 发布

阅读量628

点赞数

全文共4371个字，22张图，预计阅读时间25分钟。

这是很早之前面的，第一次面数据分析的面试，当时还傻乎乎的以为数据分析和数据挖掘是一回事呢。结果才发现，数据分析岗位大多注重的是数据库的能力，比如sql语句的考察，hive的考察，以及一些运营思维的考察，所以第一次面试就很悲剧啦，不过题目还是很有代表性的。其他的不写了，这里只分享一个关于sql的题目。

问题引出

现在有两个数据表，一个数据表记录司机的信息，比如司机id，司机姓名，司机注册时间等等，一个数据表记录一天的订单情况，比如订单ID，订单司机id，订单时间。写sql语句，返回每个司机今天最早的一笔订单。两个数据表如下图所示：

640?wx_fmt=png

用户表userinfo

640?wx_fmt=jpeg

订单表orderinfo

错误思路

好了，模拟的数据我们准备完毕了，接下来我们就要开动脑筋解决这个问题了，想了半天，脑子里蹦出这么一个想法，这不很简单么，我们先把两个表链接起来，然后按照用户进行一个分组，然后对数据排序，最后输出第一个记录不就好了，所以，我们写了如下的sql语句：

select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid order by orderinfo.ordertime limit 1

信心满满地运行，发现报错了！

640?wx_fmt=jpeg

这个only_full_group_by是什么鬼哟。百度了一下，这好像是mysql5.7版本的新特性，按照网上的方法，执行如下的sql语句就可以取消这个模式：

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

再次执行我们上面的sql语句，就当我们以为大功告成之时，没想到结果只输出了一条，而不是所有的用户的每一条记录：

640?wx_fmt=jpeg

我们来探究一下原因，是什么出现了问题，我们一步步分析，首先我们执行表链接语句：

select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid

结果正确，输出如下：

640?wx_fmt=jpeg

随后我们加入group by 语句：

select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid

在groupby语句的时候，已经是返回每个用户的一条记录了：

640?wx_fmt=jpeg

剩下的两不就不用解释了，order by将上面返回的三条记录进行一个排序，最后limit 1返回了一条结果。所以我们找到了问题所在，就是这个group by的问题，它只能返回每一组的一行。

你可能会想，既然groupby只能返回一行，我们返回min(ordertime)不就好了：

select name,sex,register,min(ordertime) from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid

结果输出为：

640?wx_fmt=jpeg

哇，结果是对的哎。结果真是对的么？如果我们在orderinfo里面加入了新的一列，乘客姓名，将orderinfo表变为如下的形式：

640?wx_fmt=jpeg

实在没有多余的脑细胞去想复杂的名字了，不过这已经足以让我们来解决问题了。继续运行上面的代码,结果如下：

640?wx_fmt=jpeg

结果并不对，张三的第一个用户应该是二号，细心的你可能已经发现问题了，还是group by的问题，它返回的是链接之后分组的第一条记录，min(ordertime)相当于是不在表中的一个新加入的字段，它的值通过min函数计算而来，所以会出现上面的结果。

看似正确的思路

那么解决这个问题的正确姿势是什么呢？在融360面试的时候，我被问到了类似的问题，吸取在滴滴面试的教训，我们用一个子查询来解决这个问题：

select name,sex,register,ordertime,orderuser from userinfo,orderinfo where userinfo.id = orderinfo.userid and orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid)

结果输出如我们所预料：

640?wx_fmt=jpeg

可以看到，子查询返回的每个用户的当天最早的订单时间，然后外层查询用一个in，返回订单时间在最早订单时间列表里的记录。

当然，你可能会说，有的司机可能没有订单，但我们也想要返回这个司机的信息，比如我在userinfo表里添加一条龙六的信息，这时候，用如上的语句就不行了，因为上面相当于内链接，我们这时候要考虑左外链接，语句变为：

select name,sex,register,ordertime,orderuser from userinfo left outer join orderinfo on userinfo.id = orderinfo.userid where orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid)

结果输出如下：

640?wx_fmt=jpeg

咦，结果输出好像跟我们之前没有什么差别？这是为什么呢？细心的你可能发现了，我们用了where子句限定了ordertime的取值范围，所以不会出现那些没有订单信息的用户，所以我们还要对语句作如下修改，让ordertime可以为Null值：

select name,sex,register,ordertime,orderuser from userinfo left outer join orderinfo on userinfo.id = orderinfo.userid where orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid) or orderinfo.ordertime is Null

结果正确输出：

640?wx_fmt=jpeg

再思考

就当我信心满满以为这样就结束并把帖子发出去的时候，群里的专家提出了疑问，如果有不同司机在同一时间接了订单怎么办？记录可能如下：

640?wx_fmt=jpeg

这时候我们就会发现，运行上面的代码，某些用户会返回两条数据，因为两个用户在同一时间接到了订单，对于其中一个用户来说，是当天的第一笔，对于另一个用户来说，不是当天的第一笔，这样就会出现问题了：

640?wx_fmt=jpeg

真正的正确思路

三表链接

大佬提供了两种解决方案，一种是把子查询的结果作为一个新表，然后利用三表链接：

select name,sex,register,ordertime,orderuser from userinfo,orderinfo,(select userid,min(ordertime) as ordertime2 from orderinfo group by userid) as orderinfo2 where userinfo.id = orderinfo.userid and orderinfo.ordertime = orderinfo2.ordertime2 and orderinfo.userid = orderinfo2.userid

结果如下：

640?wx_fmt=jpeg

使用over函数（MySQL不支持）

上面的结果是正确的，不过太麻烦了吧，于是我们还有第二种解决方案，使用row_number()/rank()/dense_rank() over(partition by)，这个在mysql中并没有实现，在oracle或者sql server中是有实现的。

不过我们还是要来看一下这一语法的基本用法：

over()函数：

over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组,并按照
例如：employees表中，有两个部门的记录：department_id ＝10和20
select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。

row_number()函数

row_number()函数用于返回根据over函数分组排序结果的编号。例如row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)
比如下面的例子中，我们按照部门进行分组，然后按照薪水进行降序排序，最后一列表示排序后的组内排名。

640?wx_fmt=jpeg

row_number()在我们这道题目的背景下是适用的，不过在其他的场景，比如按照每个部分进行分组，再按照工人的薪资进行降序排序，如果有两个人的薪资相同，这两个人的row_number值不会相同，这种情况下row_number()函数就不再适用，我们可以考虑rank()或者dense_rank()函数与over函数结合使用。

不过，这也引出了row_number()函数另一个比较有趣的作用，根据某几列进行去重：假设表TAB中有a,b,c三列，可以使用下列语句删除a,b,c都相同的重复行。

DELETE FROM (select year,QUARTER,RESULTS,row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO FROM SALE )   WHERE ROW_NO>1

rank（）函数

rank() over()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

640?wx_fmt=jpeg

dence_rank()函数

dense_rank() over()是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的．

640?wx_fmt=jpeg

好了，回到我们的题目，在oracle等其他数据库中，我们可以用下面的语法进行解决，但很遗憾，mysql不行(下面的语句没有真正测试过，因为在mysql环境中无法执行，如果有错误，欢迎大家指正！）

select name,sex,register,ordertime,orderuser from userinfo,（select *,row_number() over(PARTITION by userid order by ordertime) as tn from orderinfo) as t1 where userinfo.id = t1.userid and t1.tn = 1

上面语句中的row_number()完全可以换做rank()或者dense_rank()。同时，使用上面这种语法，不仅仅是最早的一笔订单，最早的5笔，10笔都可以计算出，功能十分强大。

mysql模拟实现rank_over

mysql没有row_number()/rank()/dense_rank() over(partition by)这样高级的sql语法，不过我们可以通过编程的方式来模拟实现类似的功能，下面给出了具体的代码：

select u.name,u.sex,u.register,o.ordertime,o.orderuser from userinfo as u,(select orderinfo.*,if(@userid = orderinfo.userid,@rank:=@rank+1,@rank:=1) as rank,@userid:=orderinfo.userid from orderinfo,(select @rank:=0,@userid:=NULL) as b order by orderinfo.userid,orderinfo.ordertime) as o where u.id = o.userid and o.rank = 1

输出结果正确如下：

640?wx_fmt=jpeg

上面的代码中用到了mysql变量的知识，首先，我们大体讲一下mysql中变量的相关知识。

mysql变量

mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。

第一种用法：set @num=1; 或set @num:=1; //这里要使用变量来保存数据，直接使用@num变量
第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……
注意上面两种赋值符号，使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值”

使用变量添加行号

我们可以设置一个初始行号，接下来在 select语句中不断改变行号的值即可：

set @i = 0;
select   (@i:=@i+1)  as   i,userinfo.*   from   userinfo

如果使用一句话，我们可以将设置初始值的过程放在from后面：

select   (@i:=@i+1)  as   i,userinfo.*   from   userinfo,(select   @i:=0)   as  it

运行效果如下：

640?wx_fmt=jpeg

上面的效果得以实现，得益于mysql中变量在select被循环赋值的特性，即每取出一行，i的值都会变化一次，而在sql server中，i不会被循环赋值，所有列的值都是最后一次的i值。

if语句

mysql中if语句的语法如下：

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
比如下面的例子,我们根据司机的注册时间划分司机类型：

select name,if(register > '2017-08-05','A','B') as type from userinfo

输出结果为：

640?wx_fmt=png

有了上面的知识储备之后，我们之前实现的mysql语句也就不难理解了，我们首先在内部生成了一个新表o，新表o对司机进行了分组，并按照接单时间先后进行了排序：

select orderinfo.*,if(@userid = orderinfo.userid,@rank:=@rank+1,@rank:=1) as rank,@userid:=orderinfo.userid from orderinfo,(select @rank:=0,@userid:=NULL) as b

可以看到，内部查询的输出如下，接下来就是简单的两表链接和筛选了：

640?wx_fmt=jpeg

总结

学习是一个不断循环迭代的过程，这道题从最初的在滴滴面试中幼稚的group by 想法，到融360面试时自认为正确的解法，再到被大佬质疑从而继续思考，最终到基本get到解题的正确姿势，经历了一系列迭代的过程。

希望我们在以后的学习过程中，能够不断的举一反三，将知识一步步的掌握扎实。

参考文章

ROW_NUMBER() OVER()函数用法：http://www.cnblogs.com/alsf/p/6344197.html
mysql实现oracle分析函数功能 over：http://blog.csdn.net/mengtianyalll/article/details/45767603
MySql 申明变量以及赋值：
http://www.cnblogs.com/qixuejia/archive/2010/12/21/1913203.html
mysql数据库实现类似标记序号的伪列：
http://blog.csdn.net/ystyaoshengting/article/details/6904627
MySQL的if，case语句使用总结：
http://outofmemory.cn/code-snippet/1149/MySQL-if-case-statement-usage-summary

原文链接：https://mp.weixin.qq.com/s?__biz=MzI1MzY0MzE4Mg==&mid=2247483773&idx=1&sn=d2578386819d66147f09209ecc834436&chksm=e9d011bcdea798aa21d65f935567eb1c66511d2fc9925b51ccea6a4985a21d85d95c01a513b3&scene=21#wechat_redirect