我的table:Field5|field19
DR|1空|2空|3Td|4Td|5空|6SQL查询应提供以下结果:Field5|field19
DR|1DR|2DR|3Td|4Td|5Td|6
olhwl3o21#
窗口函数将有助于:
select *, max(field5) over (order by field19) from table t;
您还可以使用CORATED子查询:
select t.*, (select t1.field5 from table t1 where t1.field19 <= t.field19 and t1.field5 is not null order by t1.field19 desc limit 1 ) from table t;
xv8emn3q2#
您可以使用它,它会找到field5的前一个非null值(如果它存在):
field5
null
select coalesce(t.field5, ( select tt.field5 from tablename tt where tt.field19 = ( select max(tablename.field19) from tablename where tablename.field19 < t.field19 and tablename.field5 is not null) ) ) as field5, t.field19 from tablename t
架构(SQLite v3.26)
CREATE TABLE tablename ( field5 TEXT, field19 INTEGER ); insert into tablename (field5, field19) values ('Dr1', 1), (null, 2), (null, 3), ('Td', 4), ('Td', 5), (null, 6), (null, 7),('Dr1', 8),(null, 9),('Td', 10),(null, 11),(null, 12);
查询#1
select * from tablename; | field5 | field19 | | ------ | ------- | | Dr1 | 1 | | | 2 | | | 3 | | Td | 4 | | Td | 5 | | | 6 | | | 7 | | Dr1 | 8 | | | 9 | | Td | 10 | | | 11 | | | 12 |
查询#2
select coalesce(t.field5, ( select tt.field5 from tablename tt where tt.field19 = ( select max(tablename.field19) from tablename where tablename.field19 < t.field19 and tablename.field5 is not null) ) ) as field5, t.field19 from tablename t; | field5 | field19 | | ------ | ------- | | Dr1 | 1 | | Dr1 | 2 | | Dr1 | 3 | | Td | 4 | | Td | 5 | | Td | 6 | | Td | 7 | | Dr1 | 8 | | Dr1 | 9 | | Td | 10 | | Td | 11 | | Td | 12 |
View on DB Fiddle
2条答案
按热度按时间olhwl3o21#
窗口函数将有助于:
您还可以使用CORATED子查询:
xv8emn3q2#
您可以使用它,它会找到
field5
的前一个非null
值(如果它存在):架构(SQLite v3.26)
查询#1
查询#2
View on DB Fiddle