一:今天做一个记录,在oracle或者国产数据库中如何使用 in 来自定义排序
首先,在mysql中 in 语句中自定义排序如下
set @rownum = 0;
update table_name
SET sequence = (
select @rownum := @rownum +1 as nid
)
WHERE 1=1 order by id
// 在mysql中 这条语句可以正常执行,update 和 roder by 是先排序再修改
二:但是在oracle 或者国产数据库中,update 和 order by 是不可以一起使用的,会报语法解析错误
所以,经过我在百度和在qq群询问别人之后,得到了一个方法,如何做到,先排序再赋值,并且是自增列序列号,sql如下:
本人亲测有效
update (
select
row_number() over(order by decode(name, 'dee6acfc029d40c49e61977ed46ae37d',1, 'a91c4bb7802c4222ba4236cb4adc61cb',2, 'cffcbd69f2e94a48a553420dddd4c617',3) asc) num
, *
from aaa
where name in ('dee6acfc029d40c49e61977ed46ae37d', 'a91c4bb7802c4222ba4236cb4adc61cb', 'cffcbd69f2e94a48a553420dddd4c617')
order by
decode(name, 'dee6acfc029d40c49e61977ed46ae37d',1, 'a91c4bb7802c4222ba4236cb4adc61cb',2, 'cffcbd69f2e94a48a553420dddd4c617',3)
)
set id3 = num