首頁 資料庫 mysql教程 某列为空时选择前面的非空值

某列为空时选择前面的非空值

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!=&#39;&#39; 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!=&#39;&#39; then b.uname
else (select a.uname from tb_test a where a.uname!=&#39;&#39; 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!=&#39;&#39; then b.uname
else (select a.uname from tb_test a where a.uname!=&#39;&#39; 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 <> &#39;&#39;::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 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
1 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱門文章

倉庫:如何復興隊友
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
1 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱門文章標籤

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

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

wallpaper engine怎麼設定鎖定螢幕桌布? wallpaper engine使用方法 wallpaper engine怎麼設定鎖定螢幕桌布? wallpaper engine使用方法 Mar 13, 2024 pm 08:07 PM

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

wallpaper engine能家庭共享嗎 wallpaper engine能家庭共享嗎 Mar 18, 2024 pm 07:28 PM

wallpaper engine能家庭共享嗎

抖音10級燈牌多少錢? 10級粉絲燈牌需要幾天? 抖音10級燈牌多少錢? 10級粉絲燈牌需要幾天? Mar 11, 2024 pm 05:37 PM

抖音10級燈牌多少錢? 10級粉絲燈牌需要幾天?

wallpaper engine看片有病毒嗎 wallpaper engine看片有病毒嗎 Mar 18, 2024 pm 07:28 PM

wallpaper engine看片有病毒嗎

wallpaper engine的壁紙在哪個文件夾 wallpaper engine的壁紙在哪個文件夾 Mar 19, 2024 am 08:16 AM

wallpaper engine的壁紙在哪個文件夾

wallpaper engine耗電多嗎 wallpaper engine耗電多嗎 Mar 18, 2024 pm 08:30 PM

wallpaper engine耗電多嗎

小紅書發佈作品時間怎麼設定?發布作品時間準確嗎? 小紅書發佈作品時間怎麼設定?發布作品時間準確嗎? Mar 24, 2024 pm 01:31 PM

小紅書發佈作品時間怎麼設定?發布作品時間準確嗎?

艾爾登法環通關需要多久 艾爾登法環通關需要多久 Mar 11, 2024 pm 12:50 PM

艾爾登法環通關需要多久

See all articles