比如： xh num
1 10
2 12
3 58
4 89
5 12
6 19
7 67
8 39
9 25
10 89
11 48
12 37
13 60
我要取前面10个 不重复的记录，有重复 就往后推，这个例子 有num 的值是12 和89 是重复了，取得的结果是到序号是到12的37，怎么用SQL语言实现？
------解决思路----------------------
SELECT A, B
FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY B ORDER BY A ASC) RN
FROM TBL2 T
ORDER BY A)
WHERE RN = 1
AND ROWNUM < = 10
------解决思路----------------------
SQL> set pagesize 100;
SQL>
SQL> create table test( xh int, num int) ;
表已创建。
SQL>
SQL> begin
2 insert into test values(1 , 10) ;
3 insert into test values(2 , 12) ;
4 insert into test values(3 , 58) ;
5 insert into test values(4 , 89) ;
6 insert into test values(5 , 12) ;
7 insert into test values(6 , 19) ;
8 insert into test values(7 , 67) ;
9 insert into test values(8 , 39) ;
10 insert into test values(9 , 25) ;
11 insert into test values(10, 89) ;
12 insert into test values(11, 48) ;
13 insert into test values(12, 37) ;
14 insert into test values(13, 60) ;
15 end;
16 /
PL/SQL 过程已成功完成。
SQL> select * from test
2 /
XH NUM
---------- ----------
1 10
2 12
3 58
4 89
5 12
6 19
7 67
8 39
9 25
10 89
11 48
12 37
13 60
已选择13行。
SQL> with m as (
2 select row_number() over(partition by num order by xh) rn,t.xh from test t
3 )
4 select * from test
5 where xh not in (select xh from m where rn >1)
6 and rownum<=10
7 order by xh;
XH NUM
---------- ----------
1 10
2 12
3 58
4 89
7 67
8 39
9 25
11 48
12 37
13 60
已选择10行。
SQL>
SQL> drop table test purge ;
表已删除。
SQL>
------解决思路----------------------
with table1 as
(
select 1 xh, 10 num from dual union all
select 2 xh, 12 num from dual union all
select 3 xh, 58 num from dual union all
select 4 xh, 89 num from dual union all
select 5 xh, 12 num from dual union all
select 6 xh, 19 num from dual union all
select 7 xh, 67 num from dual union all
select 8 xh, 39 num from dual union all
select 9 xh, 25 num from dual union all
select 10 xh, 89 num from dual union all
select 11 xh, 48 num from dual union all
select 12 xh, 37 num from dual union all
select 13 xh, 60 num from dual
)
select * from (select num, min(xh) xh1 from table1 group by num order by xh1) where rownum<=10