一条分两条-优快云博客

本文链接：https://blog.youkuaiyun.com/InviteSun/article/details/6462480

本文介绍了一种使用SQL的方法来统计客户与其客户经理之间的关系，并针对特定的时间段进行拆分处理，确保每个时间段内的关系都能被准确地记录下来。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--一条拆成两条
--统计当前客户经理和客户关系，上年底客户经理和客户关系
--如果客户经理和客户关系跨越这两个时间段，那么一条分成两条
WITH
    basic_t AS (
          --客户和客户经理关系表
          SELECT 123 CUST_NO,'A' AM_NO, '20091231' start_dt , '29991231' end_dt FROM DUAL
          UNION ALL
          SELECT 456,'B', '20100401' start_dt , '29991231' end_dt FROM DUAL
          UNION ALL
          SELECT 789,'C', '20100505' start_dt , '20110101' end_dt FROM DUAL
    )

,   rela_peroid AS (
            --在统计日期和上年底的关系
            SELECT   t.cust_no
                   , t.am_no
                   , CASE WHEN      t.start_dt <= '20110425'
                               AND t.end_dt > '20110425'
                               AND t.start_dt <= SUBSTR('20110425',1,4)-1 || '1231'
                               AND t.end_dt > SUBSTR('20110425',1,4) -1 || '1231'
                               THEN 'ALL'
                          WHEN      t.start_dt <= '20110425'
                               AND t.end_dt > '20110425'
                               THEN '20110425'
                          WHEN      t.start_dt <= SUBSTR('20110425',1,4)-1 || '1231'
                               AND t.end_dt > SUBSTR('20110425',1,4)-1 || '1231'
                               THEN SUBSTR('20110425',1,4)-1 ||'1231'
                    END stat_dt
            FROM basic_t t
            WHERE(
                       t.start_dt <= '20110425' --当前
                   AND t.end_dt > '20110425'
                 )
            OR (
                       t.start_dt <= SUBSTR('20110425',1,4)-1 || '1231' --上年底
                   AND t.end_dt > SUBSTR('20110425',1,4)-1 || '1231'
                )
    )

--SELECT * FROM rela_peroid;

, one2two AS (
        SELECT 'ALL' stat_dt,decode(rownum,1,'20110425',substr('20110425',1,4) -1 || '1231') rp_dt
        from dual connect by rownum < 3;
)

SELECT   t.cust_no
       , t.am_no
       , DECODE(one2two.rp_dt,NULL,t.stat_dt,one2two.rp_dt) AS stat_dt
FROM rela_peroid t
left JOIN one2two
ON t.stat_dt = one2two.stat_dt
ORDER BY cust_no