用下一个大于零的值填充列中的零值 [英] 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屋!
查看全文