发布网友 发布时间:2022-04-22 01:58
共4个回答
懂视网 时间:2022-05-06 17:47
如题,前端时间群里有人问的这个问题,正好有空,就关注了一下。 现状 dbone=# select * from tb_test;id | uname | addr----+-------+------1 | a |2 | a |3 | |4 | |5 | |6 | bb |7 | |8 | |9 | x |10 | |(10 rows)期望结果:id | uname----+-------1 | a2
如题,前端时间群里有人问的这个问题,正好有空,就关注了一下。
现状
dbone=# select * from tb_test; id | uname | addr ----+-------+------ 1 | a | 2 | a | 3 | | 4 | | 5 | | 6 | bb | 7 | | 8 | | 9 | x | 10 | | (10 rows) 期望结果: id | uname ----+------- 1 | a 2 | a 3 | a 4 | a 5 | a 6 | bb 7 | bb 8 | bb 9 | x 10 | x (10 rows) 可以如下: dbone=# select b.id, case when b.uname!='' then b.uname else (select a.uname from tb_test a where a.uname!='' and a.id
也可以用窗口函数,如下:
dbone=# SELECT dbone-# id, uname, uname_partition, first_value(uname) over (partition by uname_partition order by id) dbone-# FROM ( dbone(# SELECT dbone(# id, dbone(# uname, dbone(# sum(case when uname is null then 0 else 1 end) over (order by id) as uname_partition dbone(# FROM tb_test dbone(# ORDER BY id ASC dbone(# ) as q; id | uname | uname_partition | first_value ----+-------+-----------------+------------- 1 | a | 1 | a 2 | a | 2 | a 3 | | 2 | a 4 | | 2 | a 5 | | 2 | a 6 | bb | 3 | bb 7 | | 3 | bb 8 | | 3 | bb 9 | x | 4 | x 10 | | 4 | x (10 rows) dbone=# iming Timing is on. dbone=# SELECT id, uname, uname_partition, first_value(uname) over (partition by uname_partition order by id) FROM ( SELECT id, uname, sum(case when uname is null then 0 else 1 end) over (order by id) as uname_partition FROM tb_test ORDER BY id ASC ) as q; id | uname | uname_partition | first_value ----+-------+-----------------+------------- 1 | a | 1 | a 2 | a | 2 | a 3 | | 2 | a 4 | | 2 | a 5 | | 2 | a 6 | bb | 3 | bb 7 | | 3 | bb 8 | | 3 | bb 9 | x | 4 | x 10 | | 4 | x (10 rows) Time: 0.805 ms dbone=# select b.id, case when b.uname!='' then b.uname else (select a.uname from tb_test a where a.uname!='' and a.idSeq Scan on tb_test b (cost=0.00..12.80 rows=10 width=82) SubPlan 1 -> Limit (cost=1.16..1.17 rows=1 width=82) -> Sort (cost=1.16..1.17 rows=3 width=82) Sort Key: a.id -> Seq Scan on tb_test a (cost=0.00..1.15 rows=3 width=82) Filter: (((uname)::text <> ''::text) AND (id < b.id)) (9 rows) Time: 1.880 ms dbone=# dbone=# dbone=# dbone=# dbone=# dbone=# explain SELECT id, uname, uname_partition, first_value(uname) over (partition by uname_partition order by id) FROM ( SELECT id, uname, sum(case when uname is null then 0 else 1 end) over (order by id) as uname_partition FROM tb_test ORDER BY id ASC ) as q; QUERY PLAN --------------------------------------------------------------------------------------- WindowAgg (cost=1.71..1.91 rows=10 width=90) -> Sort (cost=1.71..1.73 rows=10 width=90) Sort Key: q.uname_partition, q.id -> Subquery Scan on q (cost=1.27..1.54 rows=10 width=90) -> WindowAgg (cost=1.27..1.44 rows=10 width=82) -> Sort (cost=1.27..1.29 rows=10 width=82) Sort Key: tb_test.id -> Seq Scan on tb_test (cost=0.00..1.10 rows=10 width=82) (8 rows) Time: 0.770 ms
这儿只有十条记录,性能相差不大,如果记录多还是要用窗口函数来的快
热心网友 时间:2022-05-06 14:55
提取列中的非空值可以使用公式也可以利用操作技巧来完成。下面从两种方法做以演示:
公式法:
输入公式=INDIRECT("a"&SMALL(IF(NOT(ISBLANK($A$1:$A$6)),ROW($A$1:$A$6),36550),ROW(A1))),单元格引用可以根据自己的表格做相应的调整,ctrl+shift+enter组合键确定数值公式,然后向下拖动填充公式,当拖拽到数值为0时停止,删除后面的0。
2.使用定位功能,选中列数据,按住f5键跳出定位对话框,选择常量。最后将数据复制粘贴到另一行。
可根据自己的实际需求选择使用方法。
热心网友 时间:2022-05-06 16:13
这个要用,offset和match函数来完成。用match先查出一个值的位置。把这个值作为offset偏移的值。还要查第二个相同的值。就麻烦点。因为match只查一个值出现的第一个位值。所以从第二值查找的时候就是改变查找区域。如果可以的话你发过来帮你做一下。一下说不清。我的邮箱wzh2228@msn.com或QQ75912228热心网友 时间:2022-05-06 17:48
没见图二