我有个表 a
name rank1 num1 rank2 num2 rank3 num3
a 12 23 77
b 13 12 88
c 14 45 44
然后用rank() over( order by num desc )但是要根据每个num来区分啊
就是结果应该为
name rank1 num1 rank2 num2 rank3 num3
a 3 12 2 23 2 77
b 2 13 3 12 1 88
c 1 14 1 45 3 44
该如何写啊,如何把这三个rank填进去啊?
高手指点哈啊!
name rank1 num1 rank2 num2 rank3 num3
a 12 23 77
b 13 12 88
c 14 45 44
然后用rank() over( order by num desc )但是要根据每个num来区分啊
就是结果应该为
name rank1 num1 rank2 num2 rank3 num3
a 3 12 2 23 2 77
b 2 13 3 12 1 88
c 1 14 1 45 3 44
该如何写啊,如何把这三个rank填进去啊?
高手指点哈啊!
解决方案 »
- 数据问题,请高手解答,在线等,求出表A中有哪些数据不在表B中
- sql的问题
- 有些难度的报表,不定行,不定列。
- 請教該查詢怎么寫
- 在oracle 9i中通过的句子,在oracle 10g中报错“ORA-01031: 权限不足”,是版本问题还是什么问题?
- 请问:数据库和实例关闭是个什么概念,具体怎么理解?
- 大哥们帮帮兄弟,新手问题,ORACLE9I中如何插入日期型数据
- 存储过程中的in问题,急,请高手解答
- netmanager测试通过,plsql连不上的问题
- oracle10g连接报12505
- 如何实现oracle在月底自动运行指定的存储过程?
- 问一个关于rank() over( order by num desc )排名的问题
select name,row_number() over (order by num1 desc) as rank1,num1,
row_number over (order by num2 desc) as rank2,num2,
row_number() over (order by num3 desc) as rank3,num3
from a order by name;
create table s1
as
select name,row_number() over (order by num1 desc) as rank1,num1,
row_number over (order by num2 desc) as rank2,num2,
row_number() over (order by num3 desc) as rank3,num3
from a order by name; update a
set (rank1,rank2,rank3) =(select rank1,rank2,rank3 from s1
where a.name=s1.name and a.num1=s1.num1 and a.num2=s1.num2 and a.num3=s1.num3 )//确定一一对应即可drop table s1;
如果只是显示上述结果设置字段name相同 即可以了~
加上自然最准确的。--方案一 建立a表如下
SQL> select * from a;NAME NUM1 NUM2 NUM3
------ ---------- ---------- ----------
a 12 23 77
b 13 12 88
c 14 45 44SQL> desc b;
名称 是否为空? 类型
----------------------------------------- -------- ------------------
NAME VARCHAR2(6)
RANK1 NUMBER(38)
NUM1 NUMBER(38)
RANK2 NUMBER(38)
NUM2 NUMBER(38)
RANK3 NUMBER(38)
NUM3 NUMBER(38)SQL> insert into b
2 select name,rank()over(order by num1 desc) rank1,num1,
3 rank()over(order by num2 desc)rank2,num2,
4 rank()over(order by num3 desc)rank3,num3 from a
5 order by name;已创建3行。SQL> select * from b;NAME RANK1 NUM1 RANK2 NUM2 RANK3 NUM3
------ ---------- ---------- ---------- ---------- ---------- ----------
a 3 12 2 23 2 77
b 2 13 3 12 1 88
c 1 14 1 45 3 44--方案二 直接建立表c如下(如顶楼)
SQL> select * from c;
--原先的表数据
NAME RANK1 NUM1 RANK2 NUM2 RANK3 NUM3
------ ---------- ---------- ---------- ---------- ---------- ----------
a 12 23 77
b 13 12 88
c 14 45 44--查询时的语句(我建了一张表没有rank字段的a)
SQL> select name,rank()over(order by num1 desc) rank1,num1,
2 rank()over(order by num2 desc)rank2,num2,
3 rank()over(order by num3 desc)rank3,num3 from a
4 order by name;NAME RANK1 NUM1 RANK2 NUM2 RANK3 NUM3
------ ---------- ---------- ---------- ---------- ---------- ----------
a 3 12 2 23 2 77
b 2 13 3 12 1 88
c 1 14 1 45 3 44--针对更新可以采用2楼的Create Table temptestSQL> create table temptest
2 as
3 select name,rank()over(order by num1 desc) rank1,num1,
4 rank()over(order by num2 desc)rank2,num2,
5 rank()over(order by num3 desc)rank3,num3 from a
6 order by name;表已创建。--然后SQL输入
SQL> update c
2 set(rank1,rank2,rank3)=
3 (select rank1,rank2,rank3 from temptest
4 where c.name=temptest.name);已更新3行。SQL> select * from c;NAME RANK1 NUM1 RANK2 NUM2 RANK3 NUM3
------ ---------- ---------- ---------- ---------- ---------- ----------
a 3 12 2 23 2 77
b 2 13 3 12 1 88
c 1 14 1 45 3 44--如果这样书写(结果不如你所料)SQL> update c
2 set(rank1,rank2,rank3)=
3 ( select row_number()over(order by num1 desc) rank1,
4 row_number()over(order by num2 desc)rank2,
5 row_number()over(order by num3 desc) rank3 from a
6 where a.name=c.name);已更新3行。SQL> select * from c;NAME RANK1 NUM1 RANK2 NUM2 RANK3 NUM3
------ ---------- ---------- ---------- ---------- ---------- ----------
a 1 12 1 23 1 77
b 1 13 1 12 1 88
c 1 14 1 45 1 44