主页 / 问题 / 1291905
Accepted
Akalit
Asked:2022-06-05 03:13:11 +0800 CST2022-06-05 03:13:11 +0800 CST

每行按日期搜索

  • 772


有2张桌子

--Результат подзапроса (отфильтрованных клиентов)
create table fq (idclient, idblank, datenter) as
    select 1, 1, date'2021-06-03'+0.5 from dual union all  
    select 5, 4, date'2021-03-13'+0.5 from dual union all 
    select 2, 1, date'2021-04-03'+0.5 from dual

--Вся таблица
create table t (idclient, idblank, datenter) as
    select 5, 13, date'2021-01-14'+0.5 from dual union all
    select 1, 1, date'2021-06-03'+0.5 from dual union all  
    select 1, 5, date'2021-02-13'+0.5 from dual union all 
    select 2, 14, date'2021-06-03'+0.5 from dual union all  
    select 3, 12, date'2021-02-17'+0.5 from dual union all 
    select 4, 9, date'2021-04-03'+0.5 from dual union all  
    select 5, 6, date'2021-01-12'+0.5 from dual union all 
    select 6, 7, date'2021-04-03'+0.5 from dual union all  
    select 5, 4, date'2021-03-13'+0.5 from dual union all 
    select 3, 3, date'2021-04-03'+0.5 from dual union all  
    select 4, 2, date'2021-02-13'+0.5 from dual union all 
    select 2, 1, date'2021-04-03'+0.5 from dual union all
    select 1, 10, date'2021-05-25'+0.5 from dual

Суть задачи- 查明客户档案是否在 3 个月前创建。
Важно- 查看问卷建立之日起 3 个月前（针对每个客户）。
此外，该表fq是子查询的结果，因此在表中搜索时，t不能考虑重复的配置文件。

我试图用以下逻辑解决：

  1. 有必要从请求中找到客户输入问卷的最后日期fq
  2. 附表t以按其搜索
  3. 从客户的最后日期开始，您需要查看，也许还有其他 3 个月的个人资料。
select t2.idclient, case when t1.idblank is not null then 1 end as priz_m3
,t1.datenter
from (select 
 idclient, idblank, datenter
from t ) t1
left join (select
  t.idclient, max(t.datenter) as datenter
 from t  join fq on t.idclient=fq.idclient
 group by t.idclient
) t2 on t1.idclient=t2.idclient 
  and t2.datenter between add_months(t1.datenter,-3) and t1.datenter-1

预期结果

IDCLIENT   PRIZ_M3   DATENTER
1             1      25-MAY-21
2            NULL      NULL
5             1      14-JAN-21
5             1      12-JAN-21

事实上db<>fiddle

sql

2 个回答

  1. 我会这样做：

    select 
    fq.*, case 
          when add_months (fq.datenter, -3) <= prevFormIssued 
          then 'y' else 'n' end existLessAs3MonthOld
from fq
cross apply (
    select max (datenter) keep (dense_rank first order by datenter desc) prevFormIssued
    from t 
    where t.idclient = fq.idclient 
    and t.datenter < fq.datenter)

    结果（在db<>fiddle上）：

      IDCLIENT    IDBLANK DATENTER            E
---------- ---------- ------------------- -
         1          1 2021-06-03 12:00:00 y
         5          4 2021-03-13 12:00:00 y
         2          1 2021-04-03 12:00:00 n

    问题中的预期输出可能与任务描述不同。

    • 5
  2. Best Answer
    SELECT t1.idclient
      ,CASE
         WHEN t2.idclient IS NOT NULL THEN
          1
         ELSE
          NULL
       END AS is_enter_earlier -- 1 если заводилась ранее
      ,t2.datenter
  FROM fq t1
  LEFT JOIN t t2
    ON t1.idclient = t2.idclient
   AND t1.datenter != t2.datenter
   AND t2.datenter BETWEEN ADD_MONTHS(t1.datenter, -3) AND t1.datenter
 ORDER BY t1.idclient;
    • 3

相关问题