如何从增量年值中获取旧值

Question

我有两个表：

表1:

|Year|CRN|CID|Cap||201910|14|ABC1|12||201910|15|ABC1|14||201820|25|ABC1|15||201820|26|ABC1|25||201810|43|ABC1|10||201720|55|ABC1|11|

表2:

|Year|CRN|BLCK||201910|14|A1||201910|15|A1||201820|25|B2||201820|26|B2||201810|43|C3||201720|55|C4||2017年20月|95日|F5||201710|65|D4|

我想退货：

表2中CRN的表1中的CID。应该只返回一个CID，因为一年可能有多个CRN用于表1中的同一CID和表2中的同一BLCK。例如，对于201910，CRN 14和15具有相同的CID ABC1和相同的BLCK A1。所以它应该返回ABC1一次

表1中上一年所有CID CRN(ABC1)的上一年BLCK值和上限值之和。201820总是在201810之后，年份值总是随着201810、201820、201910、201920而递增…例如，对于201910，我应该得到40作为Cap的总和，因为201820年的CID ABC1有两个CRN。不确定rownumber()是否总是有效，因为我必须考虑减少Year才能从当前值返回到以前的值

我使用的是Oracle 11g。

预期输出：

|Year|CID|CurrYear Cap|CurrYear BLCK|LastYear|LastYear Cap| LastYear BLCK||201910|ABC1|26|A1|201820|40|B2|

Answer 1

您可以使用lag()函数和groupingbyyear作为

select "Year", "CID", "CurrYear Cap", "CurrYear BLCK",
"Last Year", "LastYear Cap", "LastYear BLCK"
from
(
select "Year", "CID", "CurrYear Cap", "CurrYear BLCK",
lag("Year") over (order by "Year") as "Last Year", 
lag("CurrYear Cap") over (order by "Year") "LastYear Cap",
lag("CurrYear BLCK") over (order by "Year") "LastYear BLCK",
row_number() over (order by "Year" desc) as rn
from
(
with table1(Year, CRN, CID, Cap) as
(
select 201910 ,  14   ,  'ABC1'  , 12 from dual union all 
select 201910 ,  15   ,  'ABC1'  , 14 from dual union all 
select 201820 ,  25   ,  'ABC1'  , 15 from dual union all 
select 201820 ,  26   ,  'ABC1'  , 25 from dual union all 
select 201810 ,  43   ,  'ABC1'  , 10 from dual union all 
select 201720 ,  55   ,  'ABC1'  , 11 from dual    
)  ,
table2(Year   ,  CRN  , BLCK) as
(
select 201910  ,  14   ,  'A1'   from dual union all
select 201910  ,  15   ,  'A1'   from dual union all
select 201820  ,  25   ,  'B2'   from dual union all
select 201820  ,  26   ,  'B2'   from dual union all
select 201810  ,  43   ,  'C3'   from dual union all
select 201720  ,  55   ,  'C4'   from dual union all
select 201720  ,  95   ,  'F5'   from dual union all
select 201710  ,  65   ,  'D4'   from dual
)
select max(t1.year) as "Year", 
max(t1.CID) as "CID", sum(t1.Cap) as "CurrYear Cap", max(t2.blck) as "CurrYear BLCK"           
from table1 t1
join table2 t2 on t1.year = t2.year and t1.crn = t2.crn 
group by t1.year  
) 
)
where rn = 1;
Year    CID   CurrYear Cap  CurrYear BLCK   Last Year   LastYear Cap  LastYear BLCK
------  ----  ------------  -------------   ----------  ------------  -------------- 
201910  ABC1       26             A1          201820         40             B2

如果最后的where rn = 1被order by rn替换，那么所有"年份"值的所有行都会在一个订单中列出

演示