随机生成双色球号注

分享一个基于postgre写的生成随机双色球的sql

select array_to_string( array_agg(red),’,’ ),blue
from (
select cast( regexp_split_to_table(a1||’,’||a2||’,’||a3||’,’||a4||’,’||a5||’,’||a6,’,’) as int) as red ,blue
from (
SELECT floor(random()*33+1) a1,
floor(random()*33+1) a2,
floor(random()*33+1) a3,
floor(random()*33+1) a4,
floor(random()*33+1) a5,
floor(random()*33+1) a6,
floor(random()*16+1) blue
)t
order by blue,red
)tt
group by blue
having count(DISTINCT red)>=6 – and sum(red)>=99 and sum(red)<=101

union

select array_to_string( array_agg(red),’,’ ) ,blue
from (
select cast( regexp_split_to_table(a1||’,’||a2||’,’||a3||’,’||a4||’,’||a5||’,’||a6,’,’) as int) as red ,blue
from (
SELECT floor(random()*33+1) a1,
floor(random()*33+1) a2,
floor(random()*33+1) a3,
floor(random()*33+1) a4,
floor(random()*33+1) a5,
floor(random()*33+1) a6,
floor(random()*16+1) blue
)t
order by blue,red
)tt
group by blue
having count(DISTINCT red)>=6 – and sum(red)>=99 and sum(red)<=101

union

select array_to_string( array_agg(red),’,’ ),blue
from (
select cast( regexp_split_to_table(a1||’,’||a2||’,’||a3||’,’||a4||’,’||a5||’,’||a6,’,’) as int) as red ,blue
from (
SELECT floor(random()*33+1) a1,
floor(random()*33+1) a2,
floor(random()*33+1) a3,
floor(random()*33+1) a4,
floor(random()*33+1) a5,
floor(random()*33+1) a6,
floor(random()*16+1) blue
)t
order by blue,red
)tt
group by blue
having count(DISTINCT red)>=6 – and sum(red)>=99 and sum(red)<=101

union

select array_to_string( array_agg(red),’,’ ),blue
from (
select cast( regexp_split_to_table(a1||’,’||a2||’,’||a3||’,’||a4||’,’||a5||’,’||a6,’,’) as int) as red ,blue
from (
SELECT floor(random()*33+1) a1,
floor(random()*33+1) a2,
floor(random()*33+1) a3,
floor(random()*33+1) a4,
floor(random()*33+1) a5,
floor(random()*33+1) a6,
floor(random()*16+1) blue
)t
order by blue,red
)tt
group by blue
having count(DISTINCT red)>=6 – and sum(red)>=99 and sum(red)<=101

union

select array_to_string( array_agg(red),’,’ ),blue
from (
select cast( regexp_split_to_table(a1||’,’||a2||’,’||a3||’,’||a4||’,’||a5||’,’||a6,’,’) as int) as red ,blue
from (
SELECT floor(random()*33+1) a1,
floor(random()*33+1) a2,
floor(random()*33+1) a3,
floor(random()*33+1) a4,
floor(random()*33+1) a5,
floor(random()*33+1) a6,
floor(random()*16+1) blue
)t

order by blue,red
)tt
group by blue
having count(DISTINCT red)>=6 – and sum(red)>=99 and sum(red)<=101
;