刚在论坛看到一个贴子,感觉不错,就拿来测试了下,呵~~
[@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
下面是解答方法:
现有以问题,表数据如下:
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
已写入 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 ;
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;
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
---------- - -
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
---------- - - -----------------
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/