SQL每日一题(20210407)第一次召回不计费，后续如果间隔满7天计费，否则不计费，再后续距离上一次计费满7天计费，否则不计费

time丶sand

已于 2022-05-19 19:52:57 修改

阅读量130

点赞数

分类专栏：每日一练文章标签： sql 数据库 database

于 2022-05-16 11:39:50 首次发布

本文链接：https://blog.csdn.net/weixin_44681259/article/details/124789164

版权

每日一练专栏收录该内容

119 篇文章 17 订阅

订阅专栏

SQL每日一题(20210720)
SQL每日一题(20211014)
SQL每日一题(20220408)
在这里插入图片描述

select t.UID,
       t.callback_date,
       lag(t.callback_date) over (partition by t.UID order by t.callback_date) c,
       case
           when t.callback_date - lag(t.callback_date) over (partition by t.UID order by t.callback_date) >= 7 then '计费'
           else '不计费' end as                                                   charge
from t0407 t;

在这里插入图片描述

with ct as (select a.xh, a.UID, a.CALLBACK_DATE 时间2, b.CALLBACK_DATE 时间1
            from (select *, ROW_NUMBER() over (order by curdate()) xh from T0407) a
                     left join (select *, ROW_NUMBER() over (order by curdate()) xh from T0407) b
                               on a.xh = b.xh + 1 and a.UID = b.UID)
select lsb.UID, lsb.时间2 CALLBACK_DATE, (case when 时间差 >= 7 then '计费' else '不计费' end) charge
from (select *, timestampdiff(day, ct.时间1, ct.时间2) 时间差 from ct) lsb