时间戳

首页 > 解决方案 > 如何从增量年份值中获取旧值？

sql - 如何从增量年份值中获取旧值？

问题描述

我有两张桌子：

表格1：

| 年份 | CRN | 客户识别码 | 帽 |
| 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：

| 年份 | CRN | 黑色 |
| 201910 | 14 | A1 |
| 201910 | 15 | A1 |
| 201820 | 25 | B2 |
| 201820 | 26 | B2 |
| 201810 | 43 | C3 |
| 201720 | 55 | C4 |
| 201720 | 95 | F5 |
| 201710 | 65 | D4 |

我想返回：

  1. 表 1 中的 CID，用于表 2 中的 CRN。应该只返回一个 CID，因为一年可能有多个 CRN，用于表 1 中的相同 CID 和表 2 中的相同 BLCK。例如，对于 201910，CRN 14 和 15 具有相同的 CID ABC1 和相同的 BLCK A1。所以它应该返回一次 ABC1 。
  2. 表 1 中上一年度 CID (ABC1) 的所有 CRN 的上一年度 BLCK 值和总和值。201820 总是在 201810 之后，并且年份值总是递增为 201810、201820、201910、201920 ... 例如，对于 201910，我应该得到 40 作为 Cap 的总和，因为 201820 年的 CID ABC1 有两个 CRN . 不确定 rownumber() 是否总是有效，因为我必须考虑减少 Year 以从当前值返回到以前的值。

我正在使用 Oracle 11g。

预期输出： 

| 年份 | CID |CurrYear Cap|CurrYear BLCK|去年|LastYear Cap|LastYear BLCK|
|201910 | ABC1 | 26 | A1 | 201820 | 40 | B2 |

标签： sqloraclegroup-byaggregationanalytic-functions

解决方案

您可以使用lag()function 和groupingby yearas

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，则所有“年份”值的所有行都列在一个订单中。

Demo

推荐阅读