用下一个大于零的值填充列中的零值 [英] Populate zero values in column with next value greater than zero

问题描述

我有以下Postres代码：

I have the following Postres code:

SELECT
a.assessmentid,
b.groupid
FROM wo_assessment a
LEFT JOIN wo_group_info b ON a.assessmentid = b.assessmentid
WHERE a.workorderid=2
ORDER BY a.assessmentid

返回以下结果：

 |-------------------|------------|
 |    assessmentid   |   groupid  |
 |-------------------|------------|                 
 |         5         |      5     |  
 |-------------------|------------|                 
 |         6         |      4     |
 |-------------------|------------|
 |         7         |      0     |
 |-------------------|------------|                 
 |         8         |      5     |
 |-------------------|------------|
 |         9         |      0     |
 |-------------------|------------|                 
 |        10         |      0     |
 |-------------------|------------|

我想用该列中的下一个数字填充groupid字段中的0值，那不是0。

I would like to populate the 0 values in the groupid field with the next number above in that column, that isn't 0.

例如，我希望我的表看起来像这样：

So for example I want my table to look like this:

 |-------------------|------------|
 |    assessmentid   |   groupid  |
 |-------------------|------------|                 
 |         5         |      5     |  
 |-------------------|------------|                 
 |         6         |      4     |
 |-------------------|------------|
 |         7         |      4     |
 |-------------------|------------|                 
 |         8         |      5     |
 |-------------------|------------|
 |         9         |      5     |
 |-------------------|------------|                 
 |        10         |      5     |
 |-------------------|------------|

推荐答案

这对我有用：

SELECT q.assessmentid, 
first_value(b.groupid ) over (partition by value_partition order by q.assessmentid) FROM (
 SELECT a.assessmentid,
    b.groupid ,
    sum(case when b.groupid is null then 0 else 1 end) over (order by a.assessmentid) as value_partition
FROM wo_assessment as a
LEFT JOIN wo_group_info b ON a.assessmentid = b.assessmentid
ORDER BY  a.assessmentid ) as q
LEFT JOIN wo_group_info b ON q.assessmentid = b.assessmentid

这篇关于用下一个大于零的值填充列中的零值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！