假设我有一个表 prod
数据如下:
prod_id sell_count
A 100
A 200
A 300
A 400
B 600
B 100
B 300
假设下面还有很多这样的数据其中可能prod_id不同
现在我想分别从A和B产品选出随机一条
比如
prod_id sell_count
A 100
B 600这个sql该怎么写呢
数据如下:
prod_id sell_count
A 100
A 200
A 300
A 400
B 600
B 100
B 300
假设下面还有很多这样的数据其中可能prod_id不同
现在我想分别从A和B产品选出随机一条
比如
prod_id sell_count
A 100
B 600这个sql该怎么写呢
SELECT ROW_NUMBER()OVER(PARTITION BY prod_id ORDER BY NULL) AS RN ,T1.* FROM TEST T1 ) WHERE RN = 1
select 'A' prod_id,'100' sell_count from dual
union all
select 'A','200' from dual
union all
select 'A','300' from dual
union all
select 'A','400' from dual
union all
select 'B','600' from dual
union all
select 'B','100' from dual
union all
select 'B','300' from dual
)
select t1.prod_id, t1.sell_count
from (select t.prod_id,
t.sell_count,
row_number() over(partition by t.prod_id order by dbms_random.value) rn
from t) t1 where rn=1
PROD_ID SELL_COUNT
------- ----------
A 200
B 100
from (select row_number() over(partition by prod_id order by sell_count) rn,
prod_id,
sell_count
from xx_t2) t1,
(select prod_id, round(dbms_random.value(1, count(1))) rn
from xx_t2
group by prod_id) t2
where t1.prod_id = t2.prod_id
and t1.rn = t2.rn不知是否是lz要的效果