select * from tb a where not exists(select 1 from tb b where a.group=b.group and a.value<b.value)
select a.* from tb a inner join (select group,max(value) as value from tb ) b on a.group=b.group and a.value=b.value
-------参考下我的语句,查出了结果就肯定是你想要的结果.. Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as csdn SQL> SQL> with tab as ( 2 select 1 group1 , 1 value from dual 3 union all 4 select 1 group1 , 2 value from dual 5 union all 6 select 1 group1 , 3 value from dual 7 union all 8 select 2 group1 , 1 value from dual 9 union all 10 select 2 group1 , 3 value from dual 11 union all 12 select 3 group1 , 1 value from dual 13 union all 14 select 3 group1 , 3 value from dual 15 ) 16 , 17 tab1 as 18 ( 19 select group1, value, dense_rank() over(order by value) drk 20 from (select group1, 21 value, 22 row_number() over(partition by group1 order by dbms_random.value) rn 23 from tab) 24 where rn = 1 25 ) 26 select tab1.group1, tab1.value 27 from tab1, (select max(drk) ma, count(*) cnt from tab1) tab2 28 where tab2.ma = tab2.cnt 29 / GROUP1 VALUE ---------- ---------- 3 1 1 2 2 3SQL>
with temp as( select 1 group1,1 value from dual union all select 1 group1,2 value from dual union all select 1 group1,3 value from dual union all select 2 group1,1 value from dual union all select 2 group1,3 value from dual union all select 3 group1,1 value from dual union all select 3 group1,3 value from dual ) select group1,value from( select group1,value,row_number() over(partition by group1 order by group1) rn from temp ) where rn = 1; 这样取出来,肯定没有重复的
怎么可能太多呢?顶多还有一组是group value
----------- -----------
1 2
2 3
3 1
但最好还能排序:) 所以是: group value
----------- -----------
1 2
2 1
3 3
1 1
1 2
1 3
2 1
2 3
3 1
3 3
里
group value
1 1
1 2
1 3
根据规则就是找每组之间都不重复的数据得到的
1 2 每组之间都不重复的数据 ?怎么得到的 1 2,完全不明白,请楼主解释
而是查询后的结果每组之间都不重复.比如2,3组只能选1或3,所以1组只有选2的时候最后结果
才是不相同的一组值不好意思,谢谢
where not exists(select 1 from tb b where a.group=b.group and a.value<b.value)
inner join (select group,max(value) as value from tb ) b
on a.group=b.group and a.value=b.value
-------参考下我的语句,查出了结果就肯定是你想要的结果..
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as csdn
SQL>
SQL> with tab as (
2 select 1 group1 , 1 value from dual
3 union all
4 select 1 group1 , 2 value from dual
5 union all
6 select 1 group1 , 3 value from dual
7 union all
8 select 2 group1 , 1 value from dual
9 union all
10 select 2 group1 , 3 value from dual
11 union all
12 select 3 group1 , 1 value from dual
13 union all
14 select 3 group1 , 3 value from dual
15 )
16 ,
17 tab1 as
18 (
19 select group1, value, dense_rank() over(order by value) drk
20 from (select group1,
21 value,
22 row_number() over(partition by group1 order by dbms_random.value) rn
23 from tab)
24 where rn = 1
25 )
26 select tab1.group1, tab1.value
27 from tab1, (select max(drk) ma, count(*) cnt from tab1) tab2
28 where tab2.ma = tab2.cnt
29 / GROUP1 VALUE
---------- ----------
3 1
1 2
2 3SQL>
select 1 group1,1 value from dual
union all
select 1 group1,2 value from dual
union all
select 1 group1,3 value from dual
union all
select 2 group1,1 value from dual
union all
select 2 group1,3 value from dual
union all
select 3 group1,1 value from dual
union all
select 3 group1,3 value from dual
)
select group1,value from(
select group1,value,row_number() over(partition by group1 order by group1) rn from temp
) where rn = 1;
这样取出来,肯定没有重复的