某列为空时选择前面的非空值
Jun 07, 2016 pm 04:12 PM
前端
時間
選擇
非空
如题,前端时间群里有人问的这个问题,正好有空,就关注了一下。 现状 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<b.id order by a.id desc limit 1) end from tb_test b order by b.id; 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 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=# \timing 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.id<b.id order by a.id desc limit 1) end from tb_test b order by b.id; id | uname ----+------- 1 | a 2 | a 3 | a 4 | a 5 | a 6 | bb 7 | bb 8 | bb 9 | x 10 | x (10 rows) Time: 0.920 ms dbone=# dbone=# explain select b.id, case when b.uname!='' then b.uname else (select a.uname from tb_test a where a.uname!='' and a.id<b.id order by a.id desc limit 1) end from tb_test b order by b.id; QUERY PLAN ------------------------------------------------------------------------------------ Sort (cost=12.97..12.99 rows=10 width=82) Sort Key: b.id -> Seq 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
登入後複製
这儿只有十条记录,性能相差不大,如果记录多还是要用窗口函数来的快
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱門文章
擊敗分裂小說需要多長時間?
3 週前
By DDD
倉庫:如何復興隊友
3 週前
By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前
By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
1 週前
By 尊渡假赌尊渡假赌尊渡假赌
公眾號網頁更新緩存難題:如何避免版本更新後舊緩存影響用戶體驗?
3 週前
By 王林

熱門文章
擊敗分裂小說需要多長時間?
3 週前
By DDD
倉庫:如何復興隊友
3 週前
By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前
By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
1 週前
By 尊渡假赌尊渡假赌尊渡假赌
公眾號網頁更新緩存難題:如何避免版本更新後舊緩存影響用戶體驗?
3 週前
By 王林

熱門文章標籤

記事本++7.3.1
好用且免費的程式碼編輯器

SublimeText3漢化版
中文版,非常好用

禪工作室 13.0.1
強大的PHP整合開發環境

Dreamweaver CS6
視覺化網頁開發工具

SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)

wallpaper engine怎麼設定鎖定螢幕桌布? wallpaper engine使用方法
