求连续日期大于或等于3天，并且工资大于100

Question

with a as (select 1 id,  to_date('20200301', 'yyyyMMdd') rq , 101 sal
    from dual
  union all
  select 2 ,  to_date('20200302', 'yyyyMMdd') rq , 102 sal
    from dual
  union all
  select 3 ,  to_date('20200303', 'yyyyMMdd') rq , 103 sal
    from dual
  union all
  select 4 ,  to_date('20200305', 'yyyyMMdd') rq , 104 sal
    from dual
  union all
  select 5 ,  to_date('20200306', 'yyyyMMdd') rq , 12 sal
    from dual
  union all
  select 6 ,  to_date('20200309', 'yyyyMMdd') rq , 32 sal
    from dual
  union all
  select 7 ,  to_date('20200302', 'yyyyMMdd') rq , 21 sal
    from dual
  union all
  select 8 ,  to_date('20200303', 'yyyyMMdd') rq , 32 sal
    from dual
  union all
  select 9 ,  to_date('20200304', 'yyyyMMdd') rq , 12 sal
    from dual
  union all
  select 10 ,  to_date('20200305', 'yyyyMMdd') rq , 32 sal
    from dual
  union all
  select 11 ,  to_date('20200308', 'yyyyMMdd') rq , 241 sal
    from dual
  union all
  select 12 ,  to_date('20200309', 'yyyyMMdd') rq , 212 sal
    from dual
  union all
  select 13 ,  to_date('20200311', 'yyyyMMdd') rq , 312 sal
    from dual)
   

返回结果为

 

Answer 1

SELECT
    ID,
    RQ,
    SAL 
FROM
    (
    SELECT
        *,
        DATE_SUB( RQ, INTERVAL NUM DAY ) AS results 
    FROM
        ( SELECT ID, RQ, SAL, row_number ( ) over ( ORDER BY RQ ) NUM FROM sqly WHERE SAL > 100 ) AS d 
    ) AS e 
WHERE
    results IN (
    SELECT
        results 
    FROM
        (
        SELECT
            *,
            COUNT( * ) AS con 
        FROM
            (
            SELECT
                *,
                DATE_SUB( RQ, INTERVAL NUM DAY ) AS results 
            FROM
                ( SELECT ID, RQ, SAL, row_number ( ) over ( ORDER BY RQ ) NUM FROM sqly WHERE SAL > 100 ) AS a 
            ) AS b 
        GROUP BY
            results 
        ) AS c 
    WHERE
    con >= 3 
    )