SQL怎么对换排序值这一列
ORQCLE的数据库,表里头有个列是做排序值的,网页显示的时候可以按照这个顺序显示。
现在如果要修改排序值,怎么用SQL实现。比如原来是1、2、3、4四个,现在新来一条数据,要摆在第2个位置,总不能一个个修改排序值吧……
小弟先把剩下的分数给了,等我结别的贴以后再加上。先谢过了。
------解决方案--------------------update table set displayorder=displayorder+1 where displayorder>=2
insert into table(xx,xx,displayorder) values(xx,xx,2);
------解决方案--------------------WITH TEST AS (
SELECT 1 AS RNO,'AA' AS STR FROM DUAL
UNION ALL
SELECT 2 AS RNO,'BB' AS STR FROM DUAL
UNION ALL
SELECT 3 AS RNO,'CC' AS STR FROM DUAL
UNION ALL
SELECT 4 AS RNO,'DD' AS STR FROM DUAL
UNION ALL
SELECT 5 AS RNO,'EE' AS STR FROM DUAL
)
SELECT RNO, STR
FROM TEST
WHERE RNO = 1
UNION
SELECT RNO + 1 AS RNO, STR
FROM TEST
WHERE RNO > 1
UNION
SELECT 2 AS RN, STR
FROM (SELECT ROW_NUMBER() OVER(ORDER BY RNO DESC) AS RN, RNO, STR
FROM TEST T)
WHERE RN = 1
先用着,有好的再贴上。