请问如何实现如下效果,其中num默认为1
id name num
1 aa 1
2 aa 1
3 bb 1
4 bb 1
5 bb 1
6 cc 1
7 dd 1 如何更新成下面的格式 ,其中num,根据name的内容相同而增加
id name num
1 aa 1
2 aa 1
3 bb 2
4 bb 2
5 bb 2
6 cc 3
7 dd 4
id name num
1 aa 1
2 aa 1
3 bb 1
4 bb 1
5 bb 1
6 cc 1
7 dd 1 如何更新成下面的格式 ,其中num,根据name的内容相同而增加
id name num
1 aa 1
2 aa 1
3 bb 2
4 bb 2
5 bb 2
6 cc 3
7 dd 4
---------- ---------- ----------
1 aa 1
2 aa 1
3 bb 1
4 bb 1
5 bb 1
6 cc 1
7 dd 1已选择7行。scott@ORA1> select id, name, sum(case when name = before_value then 0 else 1 end) over (order by name) num
2 from (
3 select id, name, num
4 ,lag(name, 1) over(partition by name order by name) before_value
5 from c
6 ); ID NAME NUM
---------- ---------- ----------
1 aa 1
2 aa 1
3 bb 2
4 bb 2
5 bb 2
6 cc 3
7 dd 4已选择7行。
Set Num = (Select Num
From (Select Rownum As Num, Name
From (Select Name From a Group By Name))
Where Name = t.Name);