图解面试题：如何找到破产玩家？

【题目】

下表是一个玩家某天的游戏对局时间记录以及破产记录。现在要分析出当天连续破产玩家人数，这里的连续破产概念是连续两次记录为1就算。有什么思路来判断小表中的a用户是破产玩家？

玩家ID：上表只给了a玩家，表中还有其他玩家；

对局开始时间：时间类型，游戏是每一时间开一局，所以看到玩家a的对局开局时间是9点、10点、11点等这样；

是否破产：0代表未破产，1代表破产

【解题思路】

“连续两次记录”，可以先将任意两次对局结果连接起来，然后找出符合连续条件的记录。一个表里是无法找到两次对局结果的，怎么办？

可以用交叉连接，就可以返回任意两次对局结果。

1. 交叉连接

首先我们来复习一下《猴子 从零学会sql》里讲过的交叉连接（corss join）。

如下图，是对表“text”自身进行交叉联结的结果：它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。

本题的对局表交叉连接结果如下

select * from 对局表 as A cross join 对局表 as B;

这个交叉连接的结果可以看作左边是表A，右边是表B。

2.找出连续时间

在交叉联结的结果中，下图红色框中的每一行数据，左边是“当前时间”数据，右边是“连续时间”的数据。比如第一个红色框中左边是“9：00”（当前时间），右边是“10：00”（连续时间）。

选出上图中的“A.对局开始时间比B.对局开始时间小一小时

题目要求，破产条件是：连续两次记录为1就算。换成能听懂的话，就是同时满足下面两个条件：

（1）因为游戏是每一时间开一局，所以“连续两次记录“就是：

表A中的“对局开始时间” - 表B中的“对局开始时间” = -1

（2）表A中的“否破产值“=1，表B中的“否破产值“=1

先来看第一个条件如何实现？

本表中的“对局开局时间”为时间类型，我们需要从中提取小时，需要用到时间提取的函数

extract (unit from date) 当unit分别为year（年份）, month（月份）, day（日期）, hour（小时）, minute（分）等。

例如：select extract (hour from '9:00') as 小时

9点和10点相差1小时

extract (hour from '9:00')-select (extract (hour from '10:00')

=-1

对应条件1中的 表A中的“对局开始时间” - 表B中的“对局开始时间” = -1 就是：

( extract(hour from A.对局开始时间) -  extract(hour from B.对局开始时间)  )=-1

在交叉联结的查询结果中加上where条件来筛选出符合条件1的数据

当我们要对连接结果进行筛选时，使用on语句。因此加入上一步的交叉连接结果如下：

select *from 对局表 as A cross join 对局表 as Bwhere (extract(hour from A.对局开始时间) -        extract(hour from B.对局开始时间)) = -1;

得到结果：

3. 破产结果相同为1 的数据

前面说到破产条件2是：

表A中的“否破产值“=1，表B中的“否破产值“=1

在上面查询语句中加入where子句来筛选数据：

where A.是否破产=1 and B.是否破产=1

最终的sql语句就是：

select *from 对局表 as A cross join 对局表 as Bwhere (extract(hour from A.对局开始时间) -        extract(hour from B.对局开始时间)) = -1       and A.是否破产=1        and B.是否破产=1;

查询结果如下：

因此得到a是破产玩家。

如果题目换成表中多少破产玩家？

在上面的sql已经得到了哪些玩家是破产玩家，只需要统计查询结果的表行数就可以了，例如上面的查询结果表看作表c，破玩家数量就是：

select count(*)from c;

【本题考点】

1.考察逻辑思维能力，如何将“连续”条件拆解为可以具体的问题。可以使用逻辑树分析方法将复杂问题拆解成一个个可以解决的子问题。

2.考查多表联结以及交叉联结：当我们需要两个表中任意两行数据结合时，需要用交叉连接方式。

【举一反三】

编写一个 SQL 查询，查找所有连续出现两次的数字。

参考答案：

select a.numfrom 数字 as a cross join 数字 as bwhere a.ID=b.ID +1      and a.num=b.num;

得到结果：

​