当前位置：网站首页>SQL图解面试题：如何找到破产玩家？（交叉连接）

SQL图解面试题：如何找到破产玩家？（交叉连接）

2022-08-06 12:12:00 【51CTO】

【题目】

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

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接

SQL图解面试题：如何找到破产玩家？（交叉连接）_数据_02

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

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

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

【解题思路】

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

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

1. 交叉连接

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

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

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接_03

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接_04

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

       
select * 
       
from 对局表 as A 
       
cross join 对局表 as B;
      
1.
2.
3.
4.

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接_05

SQL图解面试题：如何找到破产玩家？（交叉连接）_数据_06

SQL图解面试题：如何找到破产玩家？（交叉连接）_数据_07

2.找出连续时间

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

SQL图解面试题：如何找到破产玩家？（交叉连接）_数据_08

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接_09

选出上图中的“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 小时

SQL图解面试题：如何找到破产玩家？（交叉连接）_数据_10

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接_11

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
      
1.
2.
3.
4.

SQL图解面试题：如何找到破产玩家？（交叉连接）_sql_12

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

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

       
select *
       
from 对局表 as A 
       
cross join 对局表 as B
       
where (extract(hour from A.对局开始时间) - 
       
       extract(hour from B.对局开始时间)) = -1;
      
1.
2.
3.
4.
5.
6.

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接_13

SQL图解面试题：如何找到破产玩家？（交叉连接）_数据_14

SQL图解面试题：如何找到破产玩家？（交叉连接）_数据_15

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

前面说到破产条件2是：

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

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

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

SQL图解面试题：如何找到破产玩家？（交叉连接）_数据_16

最终的sql语句就是：

       
select *
       
from 对局表 as A 
       
cross join 对局表 as B
       
where (extract(hour from A.对局开始时间) - 
       
       extract(hour from B.对局开始时间)) = -1
       
       and A.是否破产=1 
       
       and B.是否破产=1;
      
1.
2.
3.
4.
5.
6.
7.
8.

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接_17

SQL图解面试题：如何找到破产玩家？（交叉连接）_数据_18

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接_19

因此得到a是破产玩家。

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

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

       
select count(*)
       
from c;
      
1.
2.
3.

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接_20

【本题考点】

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

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

此题还有一种解决方式就是用偏移函数去做，但是需要注意的是此题有两个条件限制，所以需要对两列都进行偏移操作，一个是时间，一个是是否破产

【举一反三】

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

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接_21

SQL图解面试题：如何找到破产玩家？（交叉连接）_交叉连接_22

       
select a.num
       
from 数字 
       
as a cross join 数字 as b
       
where a.ID=b.ID +1
       
      and a.num=b.num;
      
1.
2.
3.
4.
5.
6.