select name,sex,point,rank() over(partition by sex order by point desc) as place from table_name
create table T_POINT ( NAME VARCHAR2(20), SEX NUMBER, POINT_NUM NUMBER, PLACE NUMBER );SELECT V1.NAME, V1.SEX, V1.POINT_NUM, ROW_NUM FROM (select T.*, ROW_NUMBER() OVER(PARTITION BY SEX ORDER BY POINT_NUM DESC) ROW_NUM from t_point t) V1 NAME SEX POINT PLACE 张三 1 98 1 AA 1 66 2 BB 1 55 3 李四 2 77 1不知道是不是你想要的 我也是个新手
这只是有select的,并没有update啊。
update T_POINT set place=dense_rank() OVER(PARTITION BY SEX ORDER BY POINT_NUM DESC) 其中dense_rank是有并列排名,不跳过下一排名 比如两个第一,下面一个是第二 rank是有并列排名,跳过下一排名 比如两个第一,下面一个就是第三 row_number则是没有并列排名
-- TRY IT .. SQL> SELECT * FROM PAIWEI_TEST;NAME SEX POINTS PLACE ---- ------ ---------- ----- AA MALE 80 BB MALE 90 CC FEMALE 85 DD MALE 77 EE FEMALE 88 SQL> UPDATE PAIWEI_TEST A 2 SET PLACE = (SELECT RANKS 3 FROM (SELECT NAME, 4 DENSE_RANK() OVER(PARTITION BY SEX ORDER BY POINTS DESC) RANKS 5 FROM PAIWEI_TEST) B 6 WHERE A.NAME = B.NAME) 7 WHERE EXISTS (SELECT 1 FROM PAIWEI_TEST B WHERE A.NAME = B.NAME);5 rows updatedSQL> SELECT * FROM PAIWEI_TEST;NAME SEX POINTS PLACE ---- ------ ---------- ----- AA MALE 80 2 BB MALE 90 1 CC FEMALE 85 2 DD MALE 77 3 EE FEMALE 88 1
SQL>update table a set place=(select place from (select rowid row_id, place, rank() over(partition by sex order by point desc) place from table) b where a.rowid=b.row_id);
group by版本的SQL>update table a set place=( select point from (select point, '男' sex, rownum r_no from (select point from table where sex = '男' group by point order by point) union select point, '女' sex, rownum r_no from (select point from table where sex = '女' group by point order by point)) b where a.sex=b.sex and a.point=b.point );
from table_name
(
NAME VARCHAR2(20),
SEX NUMBER,
POINT_NUM NUMBER,
PLACE NUMBER
);SELECT V1.NAME, V1.SEX, V1.POINT_NUM, ROW_NUM
FROM (select T.*,
ROW_NUMBER() OVER(PARTITION BY SEX ORDER BY POINT_NUM DESC) ROW_NUM
from t_point t) V1 NAME SEX POINT PLACE
张三 1 98 1
AA 1 66 2
BB 1 55 3
李四 2 77 1不知道是不是你想要的 我也是个新手
set place=dense_rank() OVER(PARTITION BY SEX ORDER BY POINT_NUM DESC)
其中dense_rank是有并列排名,不跳过下一排名
比如两个第一,下面一个是第二
rank是有并列排名,跳过下一排名
比如两个第一,下面一个就是第三
row_number则是没有并列排名
dense_rank()
ORA-30483: window 函数在此禁用我用的oracle10g。请问怎么办?
SQL> SELECT * FROM PAIWEI_TEST;NAME SEX POINTS PLACE
---- ------ ---------- -----
AA MALE 80
BB MALE 90
CC FEMALE 85
DD MALE 77
EE FEMALE 88 SQL> UPDATE PAIWEI_TEST A
2 SET PLACE = (SELECT RANKS
3 FROM (SELECT NAME,
4 DENSE_RANK() OVER(PARTITION BY SEX ORDER BY POINTS DESC) RANKS
5 FROM PAIWEI_TEST) B
6 WHERE A.NAME = B.NAME)
7 WHERE EXISTS (SELECT 1 FROM PAIWEI_TEST B WHERE A.NAME = B.NAME);5 rows updatedSQL> SELECT * FROM PAIWEI_TEST;NAME SEX POINTS PLACE
---- ------ ---------- -----
AA MALE 80 2
BB MALE 90 1
CC FEMALE 85 2
DD MALE 77 3
EE FEMALE 88 1
select point from
(select point, '男' sex, rownum r_no from (select point from table where sex = '男' group by point order by point)
union
select point, '女' sex, rownum r_no from (select point from table where sex = '女' group by point order by point)) b
where a.sex=b.sex and a.point=b.point
);
rank()
ORA-30483: window 函数在此禁用
请问怎么回事??
谁能给解决下。