两段好玩的SQL[AskTom]
Tom的下面两段SQL都写得比较好玩，没有用到很复杂的函数，主要是思路很受益。第一段SQL的题目比较新奇，以前没遇到过这样的情况，Tom的答案也比较规整，基本就是自己创建一个标志位，然后在外层向左推进一位，当然用PLSQL肯定可以有更加便捷的方法。第二段SQL主要是中间的那句的思路，换我的话估计就是sum()over()之后再sign+decode了，用least+greatest真的比较精彩的逻辑。
can we move not null values into one side and null values into one side? May 11, 2004 - 7am US/Eastern
Reviewer: dmv from india
Tom
we have a table with the following structure :
inv_id addr_1 addr_2 addr_3 addr_4
1 xxx null null yyy
2 null xxx null yyy
3 null null xxx yyy
4 null null null xxx
5 xxx null yyy null
6 xxx null yyy zzz
output should be :
inv_id addr_1 addr_2 addr_3 addr_4
1 xxx yyy null null
2 xxx yyy null null
3 xxx yyy null null
4 xxx null null null
5 xxx yyy null null
6 xxx yyy zzz null
all Null values should move into one side, and all Not Null values should move into one side.
Is it possible in a single update command?
Thanks
dmv
Followup May 11, 2004 - 9am US/Eastern:
SQL
> select * from t;
ID A B C D
---------- ---------- ---------- ---------- ----------
1 1 2
2 1 2
3 1 2
4 2
5 1 2
6 1 2 3
6 rows selected.
SQL
>
SQL
> update
2 (
3 select id, olda, oldb, oldc, oldd,
4 a,
5 b,
6 decode(shift,0,c,d) c,
7 decode(shift,0,d,null) d
8 from (
9 select t.*, decode(c,null,1,0) shift
10 from (
11 select id, olda, oldb, oldc, oldd,
12 a,
13 decode(shift,0,b,1,c,2,d) b,
14 decode(shift,0,c,1,d) c,
15 decode(shift,0,d,null) d
16 from (
17 select t.*, decode(b,null,decode(c,null,2,1),0) shift
18 from (
19 select id, olda, oldb, oldc, oldd,
20 decode(shift,0,a,1,b,2,c,3,d) a,
21 decode(shift,0,b,1,c,2,d) b,
22 decode(shift,0,c,1,d) c,
23 decode(shift,0,d,1,null) d
24 from (
25 select a olda, b oldb, c oldc, d oldd, t.*,
decode(a,null,decode(b,null,decode(c,null,3,2),1),0) shift
26 from t
27 )
28 ) t
29 )
30 ) t
31 )
32 )
33 set olda = a, oldb = b, oldc = c, oldd = d
34 /
6 rows updated.
SQL
> select * from t;
ID A B C D
---------- ---------- ---------- ---------- ----------
1 1 2
2 1 2
3 1 2
4 2
5 1 2
6 1 2 3
6 rows selected.
There are probably an infinite number of ways to do it, that was just the first that popped into
mind. make sure all 4 columns are the SAME TYPE of course.
Update without a cursor January 17, 2005 - 1am US/Eastern
Reviewer: siva from India
Tom,
I have a similar question on update. I'm providing the sample script below.
create table testupdate
(sno number(4),
itemcode varchar2(8),
value number(4),
appliedvalue number(4))
insert into testupdate (sno,itemcode,value) values(1,'item1',200);
insert into testupdate (sno,itemcode,value) values(2,'item2',100);
insert into testupdate (sno,itemcode,value) values(3,'item3',300);
insert into testupdate (sno,itemcode,value) values(4,'item4',200);
insert into testupdate (sno,itemcode,value) values(5,'item5',50);
insert into testupdate (sno,itemcode,value) values(6,'item6',200);
insert into testupdate (sno,itemcode,value) values(7,'item7',400);
SNO ITEMCODE VALUE APPLIEDVALUE
-------- -------- ---------- ------------
1 item1 200
2 item2 100
3 item3 300
4 item4 200
5 item5 50
6 item6 200
7 item7 400
Now:
I'm writing a stored procedure in which I have to update the appliedvalue column of the above
table.
For this I have something called the actualValue which is stored in the variable vnum_actualValue.
This value in the variable vnum_actualValue should be distributed among the appliedvalue column as
follows.
vnum_actualValue = 1000;
After the distribution of the value 1000, the updated table should look like this:
SNO ITEMCODE VALUE APPLIEDVALUE
-------- -------- ---------- ------------
1 item1 200 200
2 item2 100 100
3 item3 300 300
4 item4 200 200
5 item5 50 50
6 item6 200 150
7 item7 400 0
( TOTAL:1000 )
Now, I can very well do this with a cursor. Is there any way to do this with a single update
statement? I'm using 9i R2.
Thanks very much in advance.
Followup January 17, 2005 - 8am US/Eastern:
SQL> select * from t;
SNO ITEMCODE VALUE APPLIEDVALUE
---------- -------- ---------- ------------
1 item1 200
2 item2 100
3 item3 300
4 item4 200
5 item5 50
6 item6 200
7 item7 400
7 rows selected.
SQL> merge into t
2 using (
3 select sno, itemcode, value,
4 greatest( least( value, :x -(sum(value) over (order by sno)-value)),0) new_appliedvalue
5 from t
6 ) X
7 on (t.sno = x.sno )
8 when matched then update set appliedvalue = new_appliedvalue
9 when not matched then insert (sno) values (null) -- NEVER can happen
10 /
7 rows merged.
SQL> select * from t;
SNO ITEMCODE VALUE APPLIEDVALUE
---------- -------- ---------- ------------
1 item1 200 200
2 item2 100 100
3 item3 300 300
4 item4 200 200
5 item5 50 50
6 item6 200 150
7 item7 400 0
7 rows selected.