c1 c2 -------------- A 11 C 31 A 14 B 21 A 32 B 42 C 34 ----------------------- select c1,c2 from tab1 order by c1,c2c1 c2 -------------- A 11 A 14 A 32 B 21 B 42 C 31 C 34 --------------------------- select c1,c2, rank() over(partition by c1 order by c1,c2) as orderNum from tab1
15:39:12 SQL> select * from tb;COL1 COL2 ---------- ---------- A aaa A aab A aac B bba B bbb B bbc B bbd C ccc已选择8行。已用时间: 00: 00: 00.47 15:39:25 SQL> select t2.col1,t2.col2,t2.id-t1.mid+1 from ( 15:39:31 2 select min(id) mid,col1 from ( 15:39:31 3 select rownum id,col1,col2 from ( 15:39:31 4 select * from tb order by col1,col2) 15:39:31 5 ) t 15:39:31 6 group by col1) t1, 15:39:31 7 (select rownum id,col1,col2 from 15:39:31 8 (select * from tb order by col1,col2) 15:39:31 9 ) t2 15:39:33 10 where t1.col1=t2.col1;COL1 COL2 T2.ID-T1.MID+1 ---------- ---------- -------------- A aaa 1 A aab 2 A aac 3 B bba 1 B bbb 2 B bbc 3 B bbd 4 C ccc 1已选择8行。已用时间: 00: 00: 00.47 15:39:38 SQL>
select a.id,a.xh,(select count(*) from test b where b.id=a.id and a.xh>=b.xh) from test a order by a.id,a.xhtest 为表名 id 为您的第一列 xh 为您的第二列
为什么提示我missing expression
cuilk(clk) 的一定可以,即使在SQLServer中也可以。select a.id,a.xh,(select count(*) from test b where b.id=a.id and a.xh>=b.xh) from test a order by a.id,a.xhtest 为表名 id 为您的第一列 xh 为您的第二列
这个应该能满足你的要求吧. SELECT col1,col2, rank() over(PARTITION BY col1 ORDER BY col1 DESC) AS orderNum FROM tblname或参考这里吧.http://community.csdn.net/Expert/topic/2675/2675804.xml?temp=.2243158
--------------
A 11
C 31
A 14
B 21
A 32
B 42
C 34
-----------------------
select c1,c2 from tab1 order by c1,c2c1 c2
--------------
A 11
A 14
A 32
B 21
B 42
C 31
C 34
---------------------------
select c1,c2,
rank() over(partition by c1 order by c1,c2) as orderNum
from tab1
---------- ----------
A aaa
A aab
A aac
B bba
B bbb
B bbc
B bbd
C ccc已选择8行。已用时间: 00: 00: 00.47
15:39:25 SQL> select t2.col1,t2.col2,t2.id-t1.mid+1 from (
15:39:31 2 select min(id) mid,col1 from (
15:39:31 3 select rownum id,col1,col2 from (
15:39:31 4 select * from tb order by col1,col2)
15:39:31 5 ) t
15:39:31 6 group by col1) t1,
15:39:31 7 (select rownum id,col1,col2 from
15:39:31 8 (select * from tb order by col1,col2)
15:39:31 9 ) t2
15:39:33 10 where t1.col1=t2.col1;COL1 COL2 T2.ID-T1.MID+1
---------- ---------- --------------
A aaa 1
A aab 2
A aac 3
B bba 1
B bbb 2
B bbc 3
B bbd 4
C ccc 1已选择8行。已用时间: 00: 00: 00.47
15:39:38 SQL>
from test a
order by a.id,a.xhtest 为表名
id 为您的第一列
xh 为您的第二列
from test a
order by a.id,a.xhtest 为表名
id 为您的第一列
xh 为您的第二列
SELECT col1,col2,
rank() over(PARTITION BY col1 ORDER BY col1 DESC) AS orderNum
FROM tblname或参考这里吧.http://community.csdn.net/Expert/topic/2675/2675804.xml?temp=.2243158