sql server 查询问题求助？

Question

table表有以下数据：当QTY=0时，PerID赋值为此行的ID:

    ID  PerID   project      Order   Qty
    1               项目A         A001    100
    2               项目A         A002    123
    3               项目A         项目A    0
    4               项目B         B001     222
    5               项目B         B002     232
    6               项目B         项目B     0

需要实现以下结果:

    ID  PerID   project      Order   Qty
    1    3           项目A         A001    100
    2    3           项目A         A002    123
    3    3           项目A         项目A    0
    4    6           项目B         B001     222
    5    6           项目B         B002     232
    6    6           项目B        项目B     0

Answer 1

with one as (
select one_id = id,one_project = project from table where qty = 0
)
select id,perid = one_id,project,order,qty from table left join one on one.one_project = project