我的表数据如下。我必须找到hh_num列刚刚打印了第三列的预期结果,如下所示。
row_num rel_no hh_num
1 0 1
2 6 1
3 8 1
4 0 4
5 0 5
6 0 6
7 2 6
8 3 6
9 6 6
10 0 10
我会像下面这样写一个查询。
当rel =0时,hh_num将与row_num相同,但当rel <>为0时,hh_num将是rel =0的row_num的最大值,但小于当前行数。
UPDATE table1
SET HH_NUM = (SELECT MAX(p.ROW_NUM) FROM table1 p WHERE p.REL = 0 and p.row_num < h.row_num )
FROM table1 h
WHERE h.HH_NUM = 0
发布于 2016-02-10 04:01:12
create temp table table1 (
row_num int
,rel_no int
,hh_num int
);
insert into table1
select 1, 0, null
union all
select 2, 6, null
union all
select 3, 8, null
union all
select 4, 0, null
union all
select 5, 0, null
union all
select 6, 0, null
union all
select 7, 2, null
union all
select 8, 3, null
union all
select 9, 6, null
union all
select 10, 0, null;
update table1 a set hh_num = sub.hh_num
from
(select h.rowid as rown, h.row_num, h.rel_no, max(p.row_num) as hh_num
from table1 h inner join
table1 p
on p.rel_no = 0 and p.row_num <h.row_num
group by h.rowid, h.row_num, h.rel_no) sub
where a.rowid=sub.rown;
update table1 set hh_num = row_num
where rel_no = 0;
https://stackoverflow.com/questions/35298065
复制相似问题