동일숫자도 뽑히나요?

10개중 5개는 A, 3개는 B, 2개는 C여야 하는 거예요?

그러면 A에서 5개 뽑고 union all B에서 3개 union all C에서 2개 하면 되는 거 아닌가요?

select int

from

(

select row_number() over(order by rand()) rn, int

from tb

where grade = 'A')

where rn <= 5

union all

select int

from

(

select row_number() over(order by rand()) rn, int

from tb

where grade = 'B')

where rn <= 3

union all

select int

from

(

select row_number() over(order by rand()) rn, int

from tb

where grade = 'C')

where rn <= 2

음 그러면 이런 식으로 하는 건요?

mysql이 안 깔려있어서 테스트가 어렵네요.

재귀쿼리 되는 버전이라면 재귀쿼리로 하면 더 간단할 거 같은데..

with t as
(
select 1 id, 'A' grd union all
select 2 id, 'B' grd union all
select 3 id, 'C' grd union all
select 4 id, 'A' grd union all
select 5 id, 'B' grd union all
select 6 id, 'C' grd union all
select 7 id, 'A' grd union all
select 8 id, 'B' grd union all
select 9 id, 'C' grd union all
select 10 id, 'A' grd union all
select 11 id, 'B' grd union all
select 12 id, 'C' grd union all
select 13 id, 'A' grd union all
select 14 id, 'B' grd union all
select 15 id, 'C' grd union all
select 16 id, 'A' grd union all
select 17 id, 'B' grd union all
select 18 id, 'C' grd union all
select 19 id, 'A' grd union all
select 20 id, 'B' grd
)
, t2 as
(
select id, grd, row_number() over(partition by grd order by rand()) rn from t
)
select id
from
(
select t2.* 
  from t2, (select rand() * 100 rval )
where case when rval between 0 and 50 then 'A'
           when rval between 50 and 80 then 'B'
           when rval between 80 and 100 then 'C'
      end = grd
and rn = 1
limit 1
)
union all
select id
from
(
select t2.* 
  from t2, (select rand() * 100 rval )
where case when rval between 0 and 50 then 'A'
           when rval between 50 and 80 then 'B'
           when rval between 80 and 100 then 'C'
      end = grd
and rn = 2
limit 1
)
union all
select id
from
(
select t2.* 
  from t2, (select rand() * 100 rval )
where case when rval between 0 and 50 then 'A'
           when rval between 50 and 80 then 'B'
           when rval between 80 and 100 then 'C'
      end = grd
and rn = 3
limit 1
)
..

위의 쿼리로 하면 A, B, C가 각각 10개 이상씩 있을 때라야 잘 나올 것 같네요.

오라클 재귀쿼리로 하면 아래와 같이 처리도 가능합니다.

(mysql도 버전이 높으면 재귀쿼리가 있을 거예요.)

with t as
(
select 1 id, 'A' grd from dual union all
select 2 id, 'B' grd from dual union all
select 3 id, 'C' grd from dual union all
select 4 id, 'A' grd from dual union all
select 5 id, 'B' grd from dual union all
select 6 id, 'C' grd from dual union all
select 7 id, 'A' grd from dual union all
select 8 id, 'B' grd from dual union all
select 9 id, 'C' grd from dual union all
select 10 id, 'A' grd from dual union all
select 11 id, 'B' grd from dual union all
select 12 id, 'C' grd from dual union all
select 13 id, 'A' grd from dual union all
select 14 id, 'B' grd from dual union all
select 15 id, 'C' grd from dual union all
select 16 id, 'A' grd from dual union all
select 17 id, 'B' grd from dual union all
select 18 id, 'C' grd from dual union all
select 19 id, 'A' grd from dual union all
select 20 id, 'B' grd from dual
)
, t2 as
(select level lv, dbms_random.value * 100 rval from dual connect by level <= 10)
, t3(id, grd, lv, ids) as
(
select a.id, a.grd, 1, ','||to_char(a.id)||','
  from t a, t2 b
where case when ceil(rval) between 0 and 50 then 'A'
           when ceil(rval) between 51 and 80 then 'B'
           when ceil(rval) between 81 and 100 then 'C'
      end = a.grd
and b.lv = 1      
and rownum = 1
union all
select a.id, a.grd, b.lv + 1 lv, b.ids||to_char(a.id)||','
  from t a, t3 b, t2 c
 where instr(b.ids,','||a.id||',') = 0
   and case when ceil(rval) between 0 and 50 then 'A'
            when ceil(rval) between 51 and 80 then 'B'
            when ceil(rval) between 81 and 100 then 'C'
       end = a.grd
   and c.lv = b.lv + 1
   and rownum = 1
)
select * from t3

이 경우에도 A가 총 7개밖에 없는데 7개를 다 뽑고 난 후 새로운 random값이 50 이하일 때 더 이상 뽑을 A가 없으므로

안 뽑게 되는 문제가 있네요. 

grd가 각각 10개 이상 있다면 문제 없겠습니다.