单从SQL语句本身来说很难简化,如果写成存储过程可以先将存入一个变量v_indexid select indexid into v_indexid from tree where id=12,然后再替换
UPDATE tree t SET indexid= (SELECT decode(t.indexid,indexid,6,t.indexid+SIGN(indexid-6)) FROM tree WHERE ID=12 AND ROWNUM<2) WHERE parentid=3 AND indexid between (select indexid from tree where id=12) and 6 ;如果在命令行窗口或sqlplus中运行,可以再简化,效率更高,因为只是为了取一次这个indexid的值而已 VAR indexid NUMBER; EXEC SELECT indexid INTO :indexid FROM tree WHERE ID=12 AND ROWNUM<2; UPDATE tree SET indexid=DECODE(indexid,:indexid,6,indexid+SIGN(:indexid)) WHERE parentid=3 AND indexid BETWEEN :indexid AND 6;
在程序块中,用变量把select indexid from tree where id=12 的结果保存起来,执行程序块即可
可以建立一个sql通讯section,声明一个临时变量保存起来...
UPDATE ( SELECT PARENTID,ID,INDEXID,(SELECT INDEXID FROM tree WHERE ID=12) OVER() AS MIN_INDEXID,6 AS MAX_INDEXID FROM TREE T ) A SET A.INDEXID=(CASE WHEN A.INDEXID=A.MIN_INDEXID THEN 6 ELSE A.INDEXID-1) WHERE A.PARENTID=3 AND A.INDEXID BETWEEN A.MIN_INDEXID AND A.MAX_INDEXID;
原打算这么写的,只扫描一遍表完成update,UPDATE ( SELECT PARENTID,ID,INDEXID,first_value(case when ID=12 then INDEXID end) OVER() AS MIN_INDEXID,6 AS MAX_INDEXID FROM TREE T ) A SET A.INDEXID=(CASE WHEN A.INDEXID=A.MIN_INDEXID THEN 6 ELSE A.INDEXID-1) WHERE A.PARENTID=3 AND A.INDEXID BETWEEN A.MIN_INDEXID AND A.MAX_INDEXID;不过oracle好象禁止这种更新(没有看到官方文档上明确禁止这种更新,是否属于实现上的遗漏确实不清楚),如果使用这个方式来更新会报ora-01733错. 所以上面的over ()是在这个基础上残留的忘了删除,如果要能运行,请删除over()
update tree set indexid = decode(indexid,(select indexid from tree where id=12),6,indexid + sign((select indexid from tree where id=12)-6) ) where parentid=3 and indexid between (select indexid from tree where id=12) and 6 ; 如果语句是上面的那样的话,从"indexid between (select indexid from tree where id=12) and 6"这个条件可以推断出来(select indexid from tree where id=12)这个查询得到的值必须小于等于6。如果是等于6,那么就是indexid从6改为6,就是什么也不改;如果是小于6,那么就是indexid=indexid-1,所以语句就等价于 update tree set indexid = indexid-1 where parentid=3 and indexid between (select indexid from tree where id=12) and 6;
实在想不到办法 我在程序中查询出来select indexid from tree where id=12这句 然后变相的解决了 ~
select indexid into v_indexid from tree where id=12,然后再替换
(SELECT decode(t.indexid,indexid,6,t.indexid+SIGN(indexid-6)) FROM tree WHERE ID=12 AND ROWNUM<2)
WHERE parentid=3 AND indexid between (select indexid from tree where id=12) and 6 ;如果在命令行窗口或sqlplus中运行,可以再简化,效率更高,因为只是为了取一次这个indexid的值而已
VAR indexid NUMBER;
EXEC SELECT indexid INTO :indexid FROM tree WHERE ID=12 AND ROWNUM<2;
UPDATE tree SET indexid=DECODE(indexid,:indexid,6,indexid+SIGN(:indexid))
WHERE parentid=3 AND indexid BETWEEN :indexid AND 6;
(
SELECT PARENTID,ID,INDEXID,(SELECT INDEXID FROM tree WHERE ID=12) OVER() AS MIN_INDEXID,6 AS MAX_INDEXID FROM TREE T
) A
SET A.INDEXID=(CASE WHEN A.INDEXID=A.MIN_INDEXID THEN 6 ELSE A.INDEXID-1)
WHERE A.PARENTID=3
AND A.INDEXID BETWEEN A.MIN_INDEXID AND A.MAX_INDEXID;
(
SELECT PARENTID,ID,INDEXID,first_value(case when ID=12 then INDEXID end) OVER() AS MIN_INDEXID,6 AS MAX_INDEXID FROM TREE T
) A
SET A.INDEXID=(CASE WHEN A.INDEXID=A.MIN_INDEXID THEN 6 ELSE A.INDEXID-1)
WHERE A.PARENTID=3
AND A.INDEXID BETWEEN A.MIN_INDEXID AND A.MAX_INDEXID;不过oracle好象禁止这种更新(没有看到官方文档上明确禁止这种更新,是否属于实现上的遗漏确实不清楚),如果使用这个方式来更新会报ora-01733错.
所以上面的over ()是在这个基础上残留的忘了删除,如果要能运行,请删除over()
where parentid=3 and indexid between (select indexid from tree where id=12) and 6 ;
如果语句是上面的那样的话,从"indexid between (select indexid from tree where id=12) and 6"这个条件可以推断出来(select indexid from tree where id=12)这个查询得到的值必须小于等于6。如果是等于6,那么就是indexid从6改为6,就是什么也不改;如果是小于6,那么就是indexid=indexid-1,所以语句就等价于
update tree set indexid = indexid-1 where parentid=3 and indexid between (select indexid from tree where id=12) and 6;