急！！求SQL

[jianglili130]

表A：

ID CLASS NEW
111 a 1
111 b 2
111 c 3
222 a 1
222 b 2
222 c 3
222 d 4
222 e 5
333 a 1
333 b 2
333 c 3
333 d 4


期望结果：

ID CLASS NEW
111 a 3
111 b 2
111 c 1
222 a 5
222 b 4
222 c 3
222 d 2
222 e 1
333 a 4
333 b 3
333 c 2
333 d 1

[jianglili130]

期望结果：NEW列倒序

[SnowMote]

ORDER BY ID, CLASS, NEW DESC

[jianglili130]

[Quote=引用 2 楼 snowmote 的回复:]
ORDER BY ID, CLASS, NEW DESC
[/Quote]


更新NEW列

[horizonlyhw]

with table_a as
(
select '111' ID ,'a' class ,'1' new from dual 
union
select '111' ID ,'b' class ,'2' new from dual 
union
select '111' ID ,'c' class ,'3' new from dual 
union
select '222' ID ,'a' class ,'1' new from dual 
union
select '222' ID ,'b' class ,'2' new from dual 
union
select '222' ID ,'c' class ,'3' new from dual 
union
select '222' ID ,'d' class ,'4' new from dual 
)
select table_a.ID,table_a.class,row_number() over(partition by table_a.ID order by table_a.ID,table_a.class desc)
from table_a
order by table_a.ID,table_a.class,table_a.new desc

[jianglili130]

再问下
UPDATE如何实现呢
？？
以ID为分组，对NEW列倒序更新

[Ade子夜]

ding!!

[horizonlyhw]

把你查询出来的这个当作一个表 然后按照id和class作为条件 更新回去不就行了么

[hudingchen]

你要更新还是要检索呢？

SQL> with table_a as
  2  (
  3  select '111' ID ,'a' class ,'1' new from dual 
  4  union
  5  select '111' ID ,'b' class ,'3' new from dual 
  6  union
  7  select '111' ID ,'c' class ,'5' new from dual 
  8  union
  9  select '222' ID ,'a' class ,'1' new from dual 
 10  union
 11  select '222' ID ,'b' class ,'2' new from dual 
 12  union
 13  select '222' ID ,'c' class ,'3' new from dual 
 14  union
 15  select '222' ID ,'d' class ,'5' new from dual 
 16  )
 17  SELECT t1.ID,
 18         t1.CLASS,
 19         t2.NEW
 20    FROM (SELECT t.*,
 21                 row_number() OVER(PARTITION BY t.ID ORDER BY t.NEW) cnt
 22            FROM table_a t) t1
 23   INNER JOIN (SELECT t.ID,
 24                      t.NEW,
 25                      row_number() OVER(PARTITION BY t.ID ORDER BY t.NEW DESC) cnt
 26                 FROM table_a t) t2 ON t1.ID = t2.ID
 27                                   AND t1.cnt = t2.cnt;

ID     CL NE
------ -- --
111    a  5
111    b  3
111    c  1
222    a  5
222    b  3
222    c  2
222    d  1

[jianglili130]

更新
UPDATE

[Eileen0124]

按8楼的把select出来的数据插入临时表 然后把原表delete，在把临时表的数据插入原表

[nightingale_e]

8楼 的漂亮 。。。