sql server

txu3uszq 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(306)

我正在为一个项目开发sql，我需要根据一些规则更新soh\u wh\u a和soh\u whu\u b。
这是表a：

| Code  | Warehouse | StockOnHand | Wh_A     | Wh_B 
----------------------------------------------------
| 001   | A         | 10          | NULL     | NULL     
| 001   | B         | 20          | NULL     | NULL     
| 003   | A         | 30          | NULL     | NULL     
| 033   | B         | 40          | NULL     | NULL

我想填充wh\u a和wh\u b列。例如，让我们处理第一行，wh_a应该与stockonhand列的值相同，因为这一行属于仓库“a”。使用updatecase-when语句很容易做到这一点。
对我来说，困难的是用第二行的stockonhand列填充wh\u b列。
table的尽头应该是这样的。

| Code  | Warehouse | StockOnHand | Wh_A     | Wh_B 
----------------------------------------------------
| 001   | A         | 10          | 10       | 20
| 001   | B         | 20          | 10       | 20
| 003   | A         | 30          | 30       | 40     
| 033   | B         | 40          | 30       | 40

这就是我到目前为止所做的。。。

update Table_A set
Wh_A = (case 
        when warehouse = 'A' then stockOnHand
        when warehouse = 'B' then ... end)
Wh_B = (case 
        when warehouse = 'A' then stockOnHand
        when warehouse = 'B' then ... end)

sql window-functions sql-server sql-update common-table-expression

来源：https://stackoverflow.com/questions/63271516/how-to-insert-a-value-from-another-row-into-a-column-sql-server

2条答案

按热度按时间

zqry0prt1#

可以使用窗口函数：

select 
    code,
    warehouse,
    stockOnHand,
    max(case when warehouse = 'A' then stockOnHand end)
        over(partition by code) wh_a,
    max(case when warehouse = 'B' then stockOnHand end)
        over(partition by code) wh_b
from table_a

很容易把它变成一个 update 使用可更新的cte进行查询：

with cte as (
    select 
        wh_a,
        wh_b
        max(case when warehouse = 'A' then stockOnHand end)
            over(partition by code) new_wh_a,
        max(case when warehouse = 'B' then stockOnHand end)
            over(partition by code) new_wh_b
    from table_a
)
update cte set wh_a = new_wh_a, wh_b = new_wh_b

赞(0）回复(0）举报 2021-07-24

kkbh8khc2#

declare @Table table (
    Code        char(3) not null,
    Warehouse   char(1) not null,
    StockOnHand int     not null,
    Wh_A        int         null,
    Wh_B        int         null,

    primary key (Code, Warehouse)
);

insert into @Table
    (Code, Warehouse, StockOnHand)
values 
    ('001', 'A', 10), 
    ('001', 'B', 20), 
    ('003', 'A', 30), 
    ('003', 'B', 40);

update t set
    Wh_A = (select top 1 StockOnHand from @Table a where a.Warehouse = 'A' and a.Code = t.Code),
    Wh_B = (select top 1 StockOnHand from @Table b where b.Warehouse = 'B' and b.Code = t.Code)
from 
    @Table t;

select * from @Table

赞(0）回复(0）举报 2021-07-24

我来回答

sql server

2条答案

相关问题

热门标签

最新问答