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

SQL:使用前一行中的值填充当前行

  •  0
  • ithoughtso  · 技术社区  · 6 年前

    我有两张桌子,一张和二张一样。你可以看到时代有差距

    table1
    date      item time amount
    ----------------------------
    1/1/2000  a    1    100
    1/1/2000  a    2    100
    1/1/2000  a    3    200
    1/1/2000  a    6    300
    1/1/2000  b    1    100
    1/1/2000  b    2    100
    1/1/2000  b    5    200
    2/1/2000  a    1    500
    2/1/2000  a    3    500
    2/1/2000  a    4    550
    

    我也有表2,我填补了空白

    table2
    date      item time amount new
    -------------------------------------------
    1/1/2000  a    1    100    N
    1/1/2000  a    2    100    N
    1/1/2000  a    3    200    N
    1/1/2000  a    4           Y  <-- added amount should be 200
    1/1/2000  a    5           Y  <-- added amount should be 200
    1/1/2000  a    6    300    N
    1/1/2000  b    1    100    N
    1/1/2000  b    2    100    N
    1/1/2000  b    3           Y  <-- added amount should be 100 
    1/1/2000  b    4           Y  <-- added amount should be 100
    1/1/2000  b    5    200    N
    2/1/2000  a    1    500    N
    2/1/2000  a    2    500    N
    2/1/2000  a    3           Y  <-- added amount should be 500
    2/1/2000  a    4    550    N
    

    update t2
    set t2.amount = t1.amount
    from table2 t2
    inner join table1 t1 on t2.date = t1.date and t1.item = t2.item
    where t2.new = 'Y'
    and t2.time > (select t2.time 
                  from table1 t3
                  where max(t3.time) < t2.time)
    
    
    update t2
    set t2.amount = t1.amount
    from table1 t1
    inner join table2 t2 on t1.date = t2.date and t1.item = t2.item
    where t2.new = 'Y' and max(t1.time) < t2.time     
    

    有人知道如何获取前一行的金额吗?光标可以工作,但这是最后的解决方案。感谢您在忙碌的一天中抽出时间来帮忙。

    添加我的创建表代码

    create table #table1  (or #table2)
    (
       date smalldatetime,
       item char(1),
       [time] int,
       amount int
       ,new char(1) -- for new row flag 
    )
    
    1 回复  |  直到 6 年前
        1
  •  2
  •   forpas    6 年前

    amount :

    update t
    set amount = (
      select amount from table2 
      where
      date = t.date and item = t.item and time = (
        select max(time) from table2
        where 
        date = t.date and item = t.item 
        and time < t.time and amount is not null and new = 'N'
      ) 
    )
    from table2 t
    where t.amount is null and t.new = 'Y'
    

    看到了吗 demo

        2
  •  0
  •   saman tr    6 年前

    select 
    *,sum(amount) over (partition by time order by time) as previous_amount_for_null_values
    from table2 
    
        3
  •  0
  •   Tomasz    6 年前

    也许这个Oracle解决方案会有所帮助(我希望sqlserver有类似rownum或类似的解决方案)。首先,对数据进行排序并获得唯一的数字(使用rownum pseudocolumn)。第二,计算每个数的最大前一个数的非空值。连接数据。

    也许有一个解析函数的解,但不,我脑子里没有。

    -- create test data
    drop table tab1;
    create table tab1 (
        ym varchar2(7),
        val number
    );
    insert into tab1 values('2016/01',3);
    insert into tab1 values('2016/04',6);
    insert into tab1 values('2016/08',4);
    insert into tab1 values('2016/09',2);
    insert into tab1 values('2016/01',5);
    insert into tab1 values('2016/09',8);
    insert into tab1 values('2016/05',7);
    insert into tab1 values('2016/12',3);
    insert into tab1(ym) values('2016/03');
    insert into tab1(ym) values('2016/11');
    insert into tab1(ym) values('2016/12');
    insert into tab1(ym) values('2016/12');
    
    -- solution
    with q0 as (-- get rownum for each row
        select a.*, rownum as rnm -- get rownums
        from (
            select *
            from tab1
            order by ym -- order by, to further get proper order numbers from rownum
        ) a
    ),
    
    q1 as (-- for each rnm get previous (maximal) rnm with non missing value
        select a.rnm, max(b.rnm) as rnm_prev
        from q0 a
        left join q0 b on a.rnm > b.rnm and b.val is not null -- get only smaller rnms with values
        group by a.rnm
    )
    
    
    select q0.ym, q0.rnm, q1.rnm_prev,
    q0.val, pv.val as val_prev, nvl(q0.val, pv.val) as val_cor
    from q0
    left join q1 on q0.rnm = q1.rnm
    left join q0 pv on q1.rnm_prev = pv.rnm
    order by q0.rnm
    
        4
  •  0
  •   BlackSwan    6 年前
    update tab2
    set tab2.amount=abc.PREV_AMOUNT
    from
    table2 tab2
    join
    (SELECT *,T2.AMOUNT PREV_AMOUNT
    FROM TABLE1 T1
    JOIN
    (
    SELECT
    MAX(TIME) TIME,DATE,ITEM,MAX(AMOUNT) AMOUNT
    FROM TABLE1
    WHERE TIME!=(SELECT MAX(TIME) FROM TABLE1
    GROUP BY DATE,ITEM)
    GROUP BY DATE,ITEM) T2
    ON T1.DATE=T2.DATE
    AND T1.ITEM=T2.ITEM
    AND T1.TIME=T2.TIME) abc
    on tab2.date=abc.date
    and tab2.item=abc.item
    where tab2.new='Y' and tab2.amount is null
    

    找到每组日期和项目的第二个最长时间。 使用此上一个金额根据日期和项目更新表2,其中new='Y'

    PS:我还没有在SQLDeveloper上运行这个查询。但我的想法应该行得通。

        5
  •  0
  •   DarkRob    6 年前

    你可以试试这个。。。

        UPDATE T SET T.Amount = ( SELECT MAX(T2.Amount) FROM table2  T2 
        WHERE T2.Amount IS NOT NULL AND T2.[time] <= T.[time] and T2.item = T.item and t2.[date]=T.[date]
                          ) from table2  as T
          WHERE T.Amount IS NULL
    

    如果工作正常,则标记为已接受。