代码之家 › 专栏 › 技术社区 › aybrady

oraclesql如何用更少的代码编写这个连接?

join oracle sql

aybrady · 技术社区 · 6 年前

你好Oracle专家我有一个关于如何正确连接两个表的问题。

我的第一个表描述了休假类别、新的最大休假延期金额所需的最短服务年限和最大延期次数。

PTRLVAC_LCAT_CODE   PTRLVAC_YEAR    PTRLVAC_ROLL_MAX_HRS
C1                  0               80                  
C1                  2               88
C1                  5               128
P3                  0               120
P3                  2               128
P3                  5               168

PEBEMPL_PIDM    PEBEMPL_HIRE_DATE   PEBEMPL_LCAT_CODE  
1234            01/09/2017          P3
3214            02/01/2014          C1

我现在的连接依赖于CTE,我不确定这是否是最简单的解决方案。 **我把这里的桌子都列为CTE了

with ptrlvac as(
    select 'C1' ptrlvac_lcat_code
          ,0 ptrlvac_year
          ,80 ptrlvac_roll_max_hrs
    from dual union all
    select 'C1', 2, 88 from dual union all
    select 'C1', 5, 128 from dual union all
    select 'P3', 0, 120 from dual union all
    select 'P3', 5, 128 from dual union all
    select 'P3', 2, 168 from dual
    ) , pebempl as(
    select 1234 pebempl_pidm
          ,to_date('09-JAN-2017', 'DD-MON-YYYY') pebempl_hire_date
          ,'P3' pebempl_lcat_code
    from dual
    UNION ALL
    select 3214, to_date('01-FEB-2014','DD-MON-YYYY'), 'C1' from dual
) ,leave as(
    select a.ptrlvac_lcat_code
          ,a.ptrlvac_year
          ,a.ptrlvac_roll_max_hrs
          ,row_number()
              over(partition by a.ptrlvac_lcat_code
                   order by a.ptrlvac_year) rn
    from ptrlvac a
    )
,leave_rules as(
    select a.ptrlvac_lcat_code
          ,a.ptrlvac_year year_start
          ,nvl(b.ptrlvac_year, 100)-1 year_end
          ,a.ptrlvac_roll_max_hrs
    from leave a
         left join leave b
         on  a.ptrlvac_lcat_code = b.ptrlvac_lcat_code
         and a.rn = b.rn - 1
    )
select distinct pebempl_pidm
          ,pebempl_hire_date
          ,floor(months_between(to_date(:seldate, 'DD-MON-YYYY'), pebempl_hire_date) / 12)  as service_years
          ,pebempl_lcat_code as lcat
          ,b.ptrlvac_roll_max_hrs
    from pebempl a
         inner join leave_rules b
         on a.pebempl_lcat_code = b.ptrlvac_lcat_code
         and floor(months_between(to_date(:seldate, 'DD-MON-YYYY'), pebempl_hire_date) / 12) between b.year_start and b.year_end

任何帮助保存一些击键将不胜感激。

提前谢谢。

3 回复 | 直到 6 年前

Bobby Durrett 6 年前

我不确定这是否符合你的要求:

select
t2.PEBEMPL_PIDM,
t1.PTRLVAC_ROLL_MAX_HRS
from test1 t1, test2 t2
where
t1.PTRLVAC_LCAT_CODE = t2.PEBEMPL_LCAT_CODE and
t1.PTRLVAC_YEAR = 
(select max(t1s.PTRLVAC_YEAR) from test1 t1s
where t1s.PTRLVAC_LCAT_CODE = t2.PEBEMPL_LCAT_CODE
and (sysdate-PEBEMPL_HIRE_DATE)/365 >= t1s.PTRLVAC_YEAR);

以下是我根据你的测试数据得出的结果:

PEBEMPL_PIDM PTRLVAC_ROLL_MAX_HRS                                               
------------ --------------------                                               
        3214                   88                                               
        1234                  120

dandarc 6 年前

午餐的时候有这样的想法,进一步减少了@BobbyDurret的回答:

select
t2.PEBEMPL_PIDM,
max(t1.PTRLVAC_ROLL_MAX_HRS)
from ptrlvac t1, pebempl t2
where
t1.PTRLVAC_LCAT_CODE = t2.PEBEMPL_LCAT_CODE and
(sysdate-PEBEMPL_HIRE_DATE)/365 >= t1.PTRLVAC_YEAR
group by t2.PEBEMPL_PIDM

假设最大小时数总是随着服务年限的增加而增加。

dandarc 6 年前

不要在单独的CTE中使用行号和过滤,而是使用lead:

with leave_rules as
(
    select a.ptrlvac_lcat_code
          ,a.ptrlvac_year as year_start
          ,a.ptrlvac_roll_max_hrs
          ,lead(ptrlvac_year,1,10000) over (partition by ptrlvac_lcat_code
                                 order by ptrlvac_year)
          as year_end
    from ptrlvac a
)
select distinct pebempl_pidm
          ,pebempl_hire_date
          ,floor(months_between(sysdate, pebempl_hire_date) / 12)  as service_years
          ,pebempl_lcat_code as lcat
          ,b.ptrlvac_roll_max_hrs
    from pebempl a
         inner join leave_rules b
         on a.pebempl_lcat_code = b.ptrlvac_lcat_code
         and floor(months_between(sysdate, pebempl_hire_date) / 12) between year_start and year_end

将你的2个CTE计算“离开规则”合并为1。我只是把sysdate作为date变量,这样我可以很容易地进行测试-您可能希望像最初那样使用bind变量。