求教oracle语句

显示全部楼层 · 2009-9-27 10:21:22

--------table1------
khh,rq1, days,rq2
a15,20090901,5,0
a16,20090903,4,0
a17,20090904,4,0
--------table2----------------
rq,flag
20090901 1
20090902 1
20090903 1
20090904 1
20090905 0
20090906 0
20090907 1
20090908 1
20090909 1
20090910 1
20090911 1
20090912 0
20090913 0
20090914 1

rq2为以rq1为基点，在table2表中找到延后的按顺序第days的日期
设想的语句，但语句不是这样写

update table1 a
set rq2=(select max(rq) from table2 b where b.rq>a.rq andROWNUM=days order by rq)

------table1 结果要为--------
khh, rq1,days, rq2
a15,20090901,5,20090908即rq2为以rq1为基点，在table2表中找到延后第days的日期
a16,20090903,4,20090909
a17,20090904,4,20090910

千问 · 2009-9-27 10:21:22

WITH v AS (SELECT rq,ROW_NUMBER() OVER(ORDER BY rq) AS RN FROM TABLE2 WHERE flag=1)
SELECT a.khh, a.rq1,a.days, v2.rq AS rq2
FROM table1 a
,v v1
,v v2
WHERE a.rq1=v1.rq
AND v2.rn-v1.rn = a.days;

千问 · 2009-9-27 10:21:22

哦，还有with 语句，学习了
但with 只能跟select ,如何跟update ?

千问 · 2009-9-27 10:21:22

把整个结果当作一个表倒是可以 update ，好复杂的说

千问 · 2009-9-27 10:21:22

MERGE INTO table1 t1
USING (WITH v AS (SELECT rq,ROW_NUMBER() OVER(ORDER BY rq) AS RN FROM TABLE2 WHERE flag=1)
SELECT a.khh, a.rq1,a.days, v2.rq AS rq2, a.rowid as rid
FROM table1 a
,v v1
,v v2
WHERE a.rq1=v1.rq
AND v2.rn-v1.rn = a.days
) n
ON (t1.ROWID = n.rid)
WHEN MATCHED THEN UPDATE set rq2=n.rq2;

千问 · 2009-9-27 10:21:22

太厉害了听说with不能用于update，delete，想不到可以这样用原帖由 newkid 于 2009-9-25 23:35 发表
MERGE INTO table1 t1
USING (WITH v AS (SELECT rq,ROW_NUMBER() OVER(ORDER BY rq) AS RN FROM TABLE2 WHERE flag=1)
SELECT a.khh, a.rq1,a.days, v2.rq AS rq2, a.rowid as rid
FROM table1 a
,v v1
,v v2
WHERE a.rq1=v1.rq
AND v2.rn-v1.rn = a.days
) n
ON (t1.ROWID = n.rid)
WHEN MATCHED THEN UPDATE set rq2=n.rq2;

千问 · 2009-9-27 10:21:22

nice

千问 · 2009-9-27 10:21:22

good

千问 · 2009-9-27 10:21:22

强大
但还是出现问题了，是我没写全，就是如果rq1发生在周六周天的，如何得到以下的结果
------table1 结果要为--------
khh, rq1,days, rq2
a15,20090901,5,20090908
a16,20090903,4,20090909
a17,20090904,4,20090910
a18,20090905,4,20090910即rq2为以rq1为基点，在table2表中找到延后第days工作日的日期

千问 · 2009-9-27 10:21:22

利用day()和nextday()