请问如何实现如下效果,其中num默认为1
id name num
1 aa 1
2 aa 1
3 bb 1
4 bb 1
5 bb 1
6 cc 1如何更新成下面的格式 ,其中num,根据name的内容相同而自增
id name num
1 aa 1
2 aa 2
3 bb 1
4 bb 2
5 bb 3
6 cc 1
id name num
1 aa 1
2 aa 1
3 bb 1
4 bb 1
5 bb 1
6 cc 1如何更新成下面的格式 ,其中num,根据name的内容相同而自增
id name num
1 aa 1
2 aa 2
3 bb 1
4 bb 2
5 bb 3
6 cc 1
解决方案 »
- java中的date类型与oracle中的date类型!!!
- 请问高手如何在存储过程中post一个xml包到某url,接着接收url返回的xml包?
- 安装oracle9i时遇到的问题-----hostdef扩展名不存在?
- 连接远程oracle数据库出现问题!!
- 存储过程中备份表 写法
- 求一sql写法,顶者有分,不够在加!
- 如何在单机上安装oracle9i???
- oracle for redhat AS3 RAC安装的Cluser问题?
- 急,遇到这个情况:ORA-00001:unique constraint (DLJF.SYS_TBL_TJDL) violated
- Oracle9i无自增字段,那按照别人写的触发器如下,在SQL*Plus中执行不对呢?帮我看看了。
- 我的oracle9i怎么都安装不上去了,咋办啦?
- 临时表问题
set num = (select RN
from (select id,
name,
row_number() over(partition by name order by id) RN
from yourtable) t
where a.id = t.id
and a.name = b.name)
---------- ---------- ----------
1 aa 1
2 aa 1
3 bb 1
4 bb 1
5 bb 1
6 cc 16 rows selectedSQL>
SQL> update t
2 set num = (select rn
3 from (select id,
4 row_number() over(partition by name order by name) rn
5 from t) b
6 where t.id = b.id);6 rows updatedSQL> select * from t; ID NAME NUM
---------- ---------- ----------
1 aa 1
2 aa 2
3 bb 1
4 bb 2
5 bb 3
6 cc 16 rows selected
set t.num = (select a.rn
from (select c.id,
row_number() over(partition by c.name order by c.id) rn
from c) a
where t.id = a.id)
SQL> select * from test; ID NAME NUM
---------- -------- ----------
1 aa 1
2 aa 1
3 bb 1
4 bb 1
5 bb 1
6 cc 16 rows selectedSQL>
SQL> select t.id, t.name, t.rn num
2 from test a,
3 (select id, name, row_number() over (partition by name order by id) rn from test) t
4 where t.id = a.id; ID NAME NUM
---------- -------- ----------
1 aa 1
2 aa 2
3 bb 1
4 bb 2
5 bb 3
6 cc 16 rows selectedSQL>