猜测下,不过我记得这个问题已经解决了,在另个帖子里 楼主的意思是:數據是 2 3 2 1 4,排序后是 1 2 2 3 4另一個數據表中的數據 (數據是 1 2 ) ,这个1 2,是顺序。把前张表的 第1 2行去掉,结果就是2 3 4了 select * from ( select col,rownum rn from table1 order by col) where rn not in (select col from table2)
SQL> WITH A AS (SELECT 2 ID FROM DUAL 2 UNION ALL 3 SELECT 3 ID FROM DUAL 4 UNION ALL 5 SELECT 2 ID FROM DUAL 6 UNION ALL 7 SELECT 1 ID FROM DUAL 8 UNION ALL 9 SELECT 4 ID FROM DUAL 10 ), 11 B AS (SELECT 2 ID FROM DUAL 12 UNION 13 SELECT 1 ID FROM DUAL) 14 SELECT ID FROM ( 15 SELECT ID,ROWNUM FROM (select ID,ROWNUM from A ORDER BY ID) 16 MINUS 17 SELECT ID,ROWNUM FROM (select ID,ROWNUM from B ORDER BY ID)) SQL> /
你的意思是表1中如果有两个2,那减的结果中就包括2是吧
楼主的意思是:數據是 2 3 2 1 4,排序后是 1 2 2 3 4另一個數據表中的數據 (數據是 1 2 ) ,这个1 2,是顺序。把前张表的 第1 2行去掉,结果就是2 3 4了
select * from (
select col,rownum rn from table1
order by col)
where rn not in (select col from table2)
2 UNION ALL
3 SELECT 3 ID FROM DUAL
4 UNION ALL
5 SELECT 2 ID FROM DUAL
6 UNION ALL
7 SELECT 1 ID FROM DUAL
8 UNION ALL
9 SELECT 4 ID FROM DUAL
10 ),
11 B AS (SELECT 2 ID FROM DUAL
12 UNION
13 SELECT 1 ID FROM DUAL)
14 SELECT ID FROM (
15 SELECT ID,ROWNUM FROM (select ID,ROWNUM from A ORDER BY ID)
16 MINUS
17 SELECT ID,ROWNUM FROM (select ID,ROWNUM from B ORDER BY ID))
SQL> /
ID
----------
2
3
4