row_number() over()分析函数用法(二)
m dual union all
select 'dg' id1,13907551205 id2 from dual union all
select 'dg' id1,13907551206 id2 from dual union all
select 'dg' id1,13907551207 id2 from dual union all
select 'dg' id1,13907551207 id2 from dual union all
select 'dc' id1,13907551209 id2 from dual union all
select 'dc' id1,13907551210 id2 from dual union all
select 'dc' id1,13907551210 id2 from dual union all
select 'dc' id1,13907551212 id2 from dual
)
实现方法：
with temp as (
select 'dg' id1,13907551201 id2 from dual union all
select 'dg' id1,13907551201 id2 from dual union all
select 'dg' id1,13907551201 id2 from dual union all
select 'dg' id1,13907551204 id2 from dual union all
select 'dg' id1,13907551205 id2 from dual union all
select 'dg' id1,13907551206 id2 from dual union all
select 'dg' id1,13907551207 id2 from dual union all
select 'dg' id1,13907551207 id2 from dual union all
select 'dc' id1,13907551209 id2 from dual union all
select 'dc' id1,13907551210 id2 from dual union all
select 'dc' id1,13907551210 id2 from dual union all
select 'dc' id1,13907551212 id2 from dual
)
select * from (
select temp.*
,row_number() over(partition by id1 order by dbms_random.random) rid1
from (
select temp.*
--,row_number() over(partition by id1 order by id1) rid1
,row_number() over(partition by id1,id2 order by id1,id2) rn
from temp
) temp
where rn=1)
where rid1<=3
但由于temp表的数据量很大，是否还有更优的SQL实现？
select * from (
select temp.*
,row_number() over(partition by id1 order by dbms_random.random) rid1
from (
select distinct id1, id2 from temp)
)
where rid1<=3;