获取订阅用户最后一次重新订阅日期的SQL查询优化

方法一：基于原逻辑的嵌套查询 #

直接将临时表的筛选逻辑作为子查询嵌套，替代原查询中的临时表引用，无需额外创建临时表：

select m.*
from (
    select p1.paidpridtlid, p1.userid, p1.datepaid 
    from paidpridtl p1
    left join paidpridtl p2 
        on p1.userid = p2.userid 
        and DATE_SUB(p1.datepaid, interval 1 month) = p2.datepaid
    where p1.amount <> 0 and p2.userid is NULL
) m
left join (
    select p1.paidpridtlid, p1.userid, p1.datepaid 
    from paidpridtl p1
    left join paidpridtl p2 
        on p1.userid = p2.userid 
        and DATE_SUB(p1.datepaid, interval 1 month) = p2.datepaid
    where p1.amount <> 0 and p2.userid is NULL
) b 
    on m.userid = b.userid 
    and m.datepaid < b.datepaid
where b.datepaid is NULL;

方法二：使用窗口函数简化查询（推荐） #

利用ROW_NUMBER()窗口函数，只需一次扫描筛选出所有订阅起始行，再为每个用户的起始行按日期倒序编号，直接取编号为1的最新行：

select paidpridtlid, userid, datepaid
from (
    select 
        p1.paidpridtlid, 
        p1.userid, 
        p1.datepaid,
        ROW_NUMBER() over (partition by p1.userid order by p1.datepaid desc) as rn
    from paidpridtl p1
    left join paidpridtl p2 
        on p1.userid = p2.userid 
        and DATE_SUB(p1.datepaid, interval 1 month) = p2.datepaid
    where p1.amount <> 0 and p2.userid is NULL
) t
where rn = 1;

补充说明 #

• 两种方法均实现了原需求的逻辑合并，避免了临时表的创建
• 窗口函数版本代码更简洁，执行效率通常更优（数据量较大时尤为明显），因为仅需扫描一次paidpridtl表完成起始行筛选，而方法一需要两次扫描

内容的提问来源于stack exchange，提问作者Jeffrey Simon