返回具有同一组中上一行值的行（Oracle Sql）

I have a tabel that looks like this:

我有一个看起来像这样的表格：

|--------+------+---------|------|
| Head   | ID   | Amount  | Rank | 
|--------+------+---------|------|
|      1 | 10   |  1000   |   1  |
|      1 | 11   |  1200   |   2  |
|      1 | 12   |  1500   |   3  |
|      2 | 20   |  3400   |   1  |
|      2 | 21   |  3600   |   2  |
|      2 | 22   |  4200   |   3  |
|      2 | 23   |  1700   |   4  |
|--------+------+---------|------|

I want a new column (New_column) that does the following:

我想要一个新列（New_column）执行以下操作：

|--------+------+---------|------|------------| 
| Head   | ID   | Amount  | Rank | New_column | 
|--------+------+---------|------|------------|
|      1 | 10   |  1000   |   1  |  1000      |   
|      1 | 11   |  1200   |   2  |  1000      |
|      1 | 12   |  1500   |   3  |  1200      |
|      2 | 20   |  3400   |   1  |  3400      |
|      2 | 21   |  3600   |   2  |  3400      |
|      2 | 22   |  4200   |   3  |  3600      |
|      2 | 23   |  1700   |   4  |  4200      |
|--------+------+---------|------|------------|

Within each Head number, if rank is not 1, takes the amount of row within the Head number with Rank number before it (Rank 2 takes the amount of Rank 1 within the same Head and Rank 3 takes the amount of Rank 2 within the same Head and so on...)

在每个头号中，如果等级不是1，则取头号内的行数与其前的等级号（等级2取同一头内的等级1的量，等级3取同一等级内的等级2的量）头等......）

I know how to fix it with a For loop in other programming languages but Don't know how to do it with SQL.

我知道如何使用其他编程语言中的For循环修复它但不知道如何使用SQL。

3 个解决方案

#1

I think you basically want lag():

我认为你基本上想要lag（）：

select t.*,
       lag(amount, 1, amount) over (partition by head order by rank) as new_column
from t;

The three-argument form of lag() allows you to provide a default value.

lag（）的三参数形式允许您提供默认值。

#2

You can join the same table(subquery) on rank-1 of derived table.

您可以在派生表的rank-1上加入相同的表（子查询）。

   select t1.*,case when t1.rank=1 then amount else t2.amount new_amount 
   from your_table t1 left join (select Head,ID,Amount,Rank from your_table) t2 
   on t1.head=t2.head and t1.rank=t2.rank-1

#3

You can use this update:

您可以使用此更新：

UPDATE your_table b
SET New_column = CASE WHEN rank = 1 then Amount
                      ELSE (select a.Amount FROM your_table a where a.ID = b.ID and a.rank = b.rank-1) END

#1