table_a:
id p_id
11 a01
22 a01
11 a01
22 a01如果用SQL语句更新为下面的数据
table_a:
id p_id
11 a02
22 a02
11 a03
22 a03
id p_id
11 a01
22 a01
11 a01
22 a01如果用SQL语句更新为下面的数据
table_a:
id p_id
11 a02
22 a02
11 a03
22 a03
ID P_ID
----------- ----------
11 a02
22 a02
11 a03
22 a03
SQL> update table_a a set a.p_id=(select 'a'||lpad(count(*)+1,2,'0') from table_a b where a.id=b.id and a.rowid>=b.rowid);
4 rows updated
SQL> select * from table_a;
ID P_ID
----------- ----------
11 a02
22 a02
11 a03
22 a03
SQL>
WITH taba AS(
SELECT '11' id, 'a01' p_id FROM dual
UNION ALL
SELECT '22','a01' FROM dual
UNION ALL
SELECT '11', 'a01' FROM dual
UNION all
SELECT '22', 'a01' FROM dual
)
,tmp1 AS(SELECT id,Count(1) cnt FROM taba GROUP BY id)
,tmp2 AS(SELECT DISTINCT a.id,a.p_id,
SubStr(a.p_id,1,2)||To_Number(SubStr(a.p_id,3,3)+LEVEL) new_p_id
FROM taba a, tmp1 b
WHERE a.id=b.id CONNECT BY LEVEL<=b.cnt)
SELECT * FROM tmp2 ;
结果:
id p_id new_p_id
---------------------
11 a01 a02
11 a01 a03
22 a01 a02
22 a01 a03
--再给一个通用的WITH taba AS(
SELECT '11' id, 'a08' p_id FROM dual
UNION ALL
SELECT '22','a08' FROM dual
UNION ALL
SELECT '11', 'a08' FROM dual
UNION all
SELECT '22', 'a08' FROM dual
UNION ALL
SELECT '11', 'a08' FROM dual
)
,tmp1 AS(SELECT id,Count(1) cnt FROM taba GROUP BY id)
,tmp2 AS(SELECT DISTINCT a.id,a.p_id,
SubStr(a.p_id,1,1)||Trim(To_Char(SubStr(a.p_id,2,3)+LEVEL,'00')) new_p_id
FROM taba a, tmp1 b
WHERE a.id=b.id CONNECT BY LEVEL<=b.cnt)
SELECT * FROM tmp2 ;结果:
id p_id new_p_id
---------------------
11 a08 a09
11 a08 a10
11 a08 a11
22 a08 a09
22 a08 a10
id p_id
11 a01
22 a01
11 a01
22 a01如何用SQL语句更新为下面的数据,有没有简单点的方法
table_a:
id p_id
11 xxx
22 xxx
11 yyy
22 yyy
4 rows updated
SQL> select * from table_a;
ID P_ID
----------- ----------
11 xxx
22 xxx
11 yyy
22 yyy
SQL>
没有太简单的方法.
--更新rowid较小的记录
update table_a a set a.p_id='xxx' where a.rowid>(select min(b.rowid) from table_a b where a.id=b.id);
--更新rowid较大的记录
update table_a a set a.p_id='yyy' where a.rowid<(select max(b.rowid) from table_a b where a.id=b.id);
这样只需要修改p_id的值就可以了,方便通用!