17,082
社区成员
发帖
与我相关
我的任务
分享
select * from
(select
ntile(5) over(partition by code order by rownum) ntile
from tt)
where ntile=1
ntile(5) 随机分5组
WITH TEMP AS
(
SELECT '移动号卡10元' NAME,'0101' CODE FROM DUAL
UNION
SELECT '移动号卡20元' NAME,'0101' CODE FROM DUAL
UNION
SELECT '移动号卡30元' NAME,'0101' CODE FROM DUAL
UNION
SELECT '移动号卡40元' NAME,'0101' CODE FROM DUAL
UNION
SELECT '联通号卡10元' NAME,'0102' CODE FROM DUAL
UNION
SELECT '联通号卡20元' NAME,'0102' CODE FROM DUAL
UNION
SELECT '联通号卡50元' NAME,'0102' CODE FROM DUAL
UNION
SELECT '联通号卡100元' NAME,'0102' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡10元' NAME,'0103' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡20元' NAME,'0103' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡30元' NAME,'0103' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡40元' NAME,'0103' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡50元' NAME,'0103' CODE FROM DUAL
UNION
SELECT 'QQ游戏点卡60元' NAME,'0103' CODE FROM DUAL
)
select t1.code,t1.name from
(select CODE,NAME,row_number() over(partition by CODE order by CODE) rm from temp group by code,name order by 1,2) t1,
(select code,ceil(max(rm)*0.2) cnt from
(select CODE,NAME,row_number() over(partition by CODE order by CODE) rm from temp group by code,name order by 1,2)
group by code) t2
where t1.code=t2.code and t1.rm<=t2.cnt
order by 1,2
这有一个写法,但不是最优的~