ITPub博客

首页 > 数据库 > Oracle > 很好的SQL

很好的SQL

原创 Oracle 作者:flgtzy 时间:2009-01-14 22:50:39 0 删除 编辑

select a1,a2,b1,b2
from
(select a1,a2,a1||a2||ROW_NUMBER() over (partition by a1||a2 order by a1||a2 ) sna from a ) x,
(select b1,b2,b1||b2||ROW_NUMBER() over (partition by b1||b2 order by b1||b2 ) snb from b ) y
where x.sna=y.snb(+)
union all
select a1,a2,b1,b2
from
(select a1,a2,a1||a2||ROW_NUMBER() over (partition by a1||a2 order by a1||a2 ) sna from a ) x,
(select b1,b2,b1||b2||ROW_NUMBER() over (partition by b1||b2 order by b1||b2 ) snb from b ) y
where x.sna(+)=y.snb
and x.sna is null[@more@]

row_number()用法:

ROW_NUMBER is an analytic function. It assigns a unique number to each row to
which it is applied (either each row in the partition or each row returned by the
query), in the ordered sequence of rows specified in the order_by_clause,
beginning with 1.
You cannot use ROW_NUMBER or any other analytic function for expr. That is, you
can use other built-in function expressions for expr, but you cannot nest analytic
functions.




test中有列:NU,值为:1,1,1,2,3,4


SQL> select a.*,row_number() over (order by nu) as rn from test a;

NU RN---------- ----------
1 1
1 2
1 3
2 4
3 5
4 6

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/7185131/viewspace-1016115/ ,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论
  • 博文量
    1
  • 访问量
    951