今天在ITPUB上看見這樣一個問題:
有一张表如下 test:
value num
15 1
37 2
48 3
2
3
想根据同表当中num相同的值 更新value为空的字段
如想把两个空的value 值 更新为 37,48
現測試數據如下:
create table a7 as (select 15 value,1 num from dual
union
select 37 value,2 num from dual
union
select 48 value,3 num from dual
union
select null value,2 num from dual
union
select null value,3 num from dual
)
方法一:
update a7 a
set value=(select value from a7 b where a.num=b.num and nvl(b.value,0)<>0)
where nvl(value,0)=0
方法二:
update (select num,value from a7 where nvl(value,0)=0) a
set value=(select value from a7 b where a.num=b.num and nvl(b.value,0)<>0)
這個看似很簡單的問題,我也想了一下,也寫過一些UPDATE語句,但是老報ORA-01427(单行子查询返回多于一行),后來自己分析了一下,總結了一些小經驗,當兩表關聯更新的時候,要更新的那個表(A表)和嵌套查詢的那個表(見紅色部門)的數據必須是一對一(兩表紀錄數一樣)或者多對一(子查詢的記錄數小于要更新的紀錄)的關系,千萬不要是一對多的關系,這樣才不會出錯!!!
第二道题是这样的:
表内容为:
code sal salnum
001 A 100
001 B 200
002 B 400
002 C 200
003 D 200
要得到如下格式的结果
code sal salnum
001 A 100
001 B 200
001 C
001 D
002 A
002 B 400
002 C 200
002 D
002 A
002 B
002 C
002 D 200
对于这样的问题,我的思维刚开始就是想凭凑出这样的结果来,但是想了半天没有想出来,后来就要自己找规律啊,记录数(12)=(DISTINCT CODE)*(DISTINCT SAL),就是相当于两个表的笛卡尔集,这样一下思路就宽广多了啊!所以在以后的工作中,一定要善于发现规律,才能更好的解决问题,老想凑结果是不行的!
SQL> WITH A AS (SELECT '001' CODE,'A' SAL,100 SALNUM FROM DUAL
2 UNION
3 SELECT '001' CODE,'B' SAL,200 SALNUM FROM DUAL
4 UNION
5 SELECT '002' CODE,'B' SAL,400 SALNUM FROM DUAL
6 UNION
7 SELECT '002' CODE,'C' SAL,200 SALNUM FROM DUAL
8 UNION
9 SELECT '003' CODE,'D' SAL,200 SALNUM FROM DUAL
10 )
11 SELECT C.CODE,C.SAL,A.SALNUM FROM (select A.CODE,B.SAL from (SELECT DISTINCT CODE FROM A) A,(SELECT DISTINCT SAL FROM A) B
12 ORDER BY CODE,SAL) C,A
13 WHERE A.CODE(+)=C.CODE AND A.SAL(+)=C.SAL
14 ORDER BY C.CODE,C.SAL
15 /
CODE SAL SALNUM
---- --- ----------
001 A 100
001 B 200
001 C
001 D
002 A
002 B 400
002 C 200
002 D
003 A
003 B
003 C
003 D 200
12 rows selected
判斷紀錄是否是連續的?
SQL> with a as(select 2014 fphm,'00000001' kshm from dual
2 union
3 select 2014 fphm,'00000002' kshm from dual
4 union
5 select 2014 fphm,'00000003' kshm from dual
6 union
7 select 2014 fphm,'00000004' kshm from dual
8 union
9 select 2014 fphm,'00000005' kshm from dual
10 union
11 select 2014 fphm,'00000007' kshm from dual
12 union
13 select 2014 fphm,'00000008' kshm from dual
14 union
15 select 2014 fphm,'00000009' kshm from dual
16 union
17 select 2013 fphm,'00000120' kshm from dual
18 union
19 select 2013 fphm,'00000121' kshm from dual
20 union
21 select 2013 fphm,'00000122' kshm from dual
22 union
23 select 2013 fphm,'00000124' kshm from dual
24 union
25 select 2013 fphm,'00000125' kshm from dual
26 )
27 SELECT b.fphm, MIN (b.kshm) Start_HM, MAX (b.kshm) End_HM
28 FROM (SELECT a.*, TO_NUMBER (a.kshm - ROWNUM) cc
29 FROM (SELECT *
30 FROM a
31 ORDER BY fphm, kshm) a) b
32 GROUP BY b.fphm, b.cc
33 order by b.fphm, b.cc
34 /
FPHM START_HM END_HM
---------- -------- --------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10768286/viewspace-246426/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10768286/viewspace-246426/