sql解惑2 谜题53 之lag

用刚在小翁先生那里学到的ignore方法

SQL> select * from foobar;

       LVL COLOR			      LENGTH	  WIDTH        HGT
---------- ------------------------------ ---------- ---------- ----------
	 1 red					   8	     10 	12
	 2								20
	 3					   9	     82 	25
	 4 blue 					     67
	 5 gray

已用时间:  00: 00: 00.13
SQL> select lvl,
coalesce(color,lag(color ignore nulls) over (order by lvl)) as color,
coalesce(length,lag(length ignore nulls) over (order by lvl)) as length,
coalesce(width,lag(width ignore nulls) over (order by lvl)) as width,
coalesce(hgt,lag(hgt ignore nulls) over (order by lvl)) as hgt
from foobar
  7  order by 1;

       LVL COLOR			      LENGTH	  WIDTH        HGT
---------- ------------------------------ ---------- ---------- ----------
	 1 red					   8	     10 	12
	 2 red					   8	     10 	20
	 3 red					   9	     82 	25
	 4 blue 				   9	     67 	25
	 5 gray 				   9	     67 	25

已用时间:  00: 00: 00.01
SQL>