MySQL自连接的应用场景

J.Q.HUO
MySQL数据库

MySQL自连接是将sql数据表与自己连接并进行查询的方式，自连接主要应用于表列数据之间的相互比较、根据单列数据的规则进行筛选的场景，说起来比较抽象，让我们看几个题来加深一下理解

首先看一下自连接的两张表长什么样子；

t1 t2
t1字段 t2字段

书写自连接的筛选条件时候，将这个图构建出来，然后注意左右表比较即可

MySQL自连接应用

leetcode 534 游戏玩法分析3
计算截止到当前日期为止的单列前缀和

考虑将两表自连接，筛选条件是t1表中的日期大等t2表中的日期，然后将筛选后的行分组求和（sum+group by）即可

select t1.player_id, t1.event_date, sum(t2.games_played) as `games_played_so_far`
from Activity as t1 
inner join Activity as t2
on t1.player_id = t2.player_id and t1.event_date >= t2.event_date
group by t1.player_id, t1.event_date

leetcode 550 游戏玩法分析4
给定一个登录日期字段，编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率

这题是典型的自连接应用，题中的筛选条件是根据单列的规则进行筛选，则需要将表自连接，然后用datediff判断左右两张表两个日期之差是否为1即可

# 从非连接的多表中选数据，把多张表放在from后面即可
select round(t3.tarPlayer / t4.totalPlayer,2) as 'fraction'
from (
    select count(distinct t1.player_id) as 'tarPlayer'
    from (
        select player_id, min(event_date) as 'firstDate'
        from Activity
        group by player_id
    ) t1
    inner join Activity as t2
    on t1.player_id = t2.player_id and datediff(t2.event_date,t1.firstDate) = 1
) as t3,
(select count(distinct player_id) as `totalPlayer` from Activity) as t4