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
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
1
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
1
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
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
1
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
1
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