巧用row_number() over()函数,选取重复记录中想要的值

刚在论坛看到一个贴子,感觉不错，就拿来测试了下，呵~~

[@more@]

贴子内容

如何从某字段重复的多条记录条件查找

求一个SQL语句写法。

现有以问题，表数据如下：

A B C
1 a x
1 a y
1 a z
2 a x
2 a z
3 a x


需要查询结果：如果A字段存在相同值,且重复记录中C字段存在值为y的记录，只取C字段值为Y的记录，如果重复记录不存在C字段为Y的记录，任取一条A字段重复的记录。
如上表所需查询结果为：

A B C 或者 A B C
1 a y 1 a y
2 a x 2 a z
3 a x 3 a x

下面是解答方法:

SQL> edit;
已写入 file afiedt.buf

1 create table t as
2 ( select 1 as a, 'a' as b, 'x' as c from dual union all
3 select 1 as a, 'a' as b, 'y' as c from dual union all
4 select 1 as a, 'a' as b, 'z' as c from dual union all
5 select 2 as a, 'a' as b, 'x' as c from dual union all
6 select 2 as a, 'a' as b, 'z' as c from dual union all
7* select 3 as a, 'a' as b, 'x' as c from dual )
8 ;

表已创建。

SQL> select a,b,c from
2 (select a,b,c,row_number()over(partition by a order by decode(c,'y',1,2),c) rn
3 from t)
4 where rn=1;

A B C
---------- - -
1 a y
2 a x
3 a x

附:详细分析下面sql结果

SQL> select t.*,decode(c,'y',1,2) from t;

A B C DECODE(C,'Y',1,2)
---------- - - -----------------
1 a x 2
1 a y 1
1 a z 2
2 a x 2
2 a z 2
3 a x 2

已选择6行。

通过order by decode(c,'y',1,2),c 这二例,巧妙将分组后c为Y的值置为最前面.

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

转载于:http://blog.itpub.net/21251711/viewspace-1019731/