rq type kshm jshm sl id
01-1月 -10 出库 000001 000100 100
01-1月 -10 出库 000101 000200 100
01-1月 -10 出库 000401 000600 600
01-1月 -10 出库 000701 000800 800
01-1月 -10 入库 100001 100100 100我现在想根据kshm排序后rownum值,修改id号,如何处理?
update test c set c.id = (
select rownum from (
select * from test b, (select rownum,kshm from test order by kshm) a
where a.kshm =b.kshm )
) d
where c.kshm=d.kshm 老是报错?? 今天最后的问题了!!
SET c.id =
(SELECT rn FROM (SELECT rownum rn, kshm FROM test ORDER BY kshm) a WHERE a.kshm = c.kshm)
where a.kshm =c.kshm )不行
不然的话,没有目的的写
update test c set c.id = select rownum from test
UPDATE test c
SET c.id =
(SELECT rn
FROM (SELECT rownum rn, kshm FROM (SELECT * FROM test ORDER BY kshm)) a
WHERE a.kshm = c.kshm);
--使用分析函数row_number()
UPDATE test c
SET c.id =
(SELECT rn
FROM (SELECT kshm, row_number() over(ORDER BY kshm) rn FROM test) a
WHERE a.kshm = c.kshm)