SQL 实现随机双色球（ 随机生成6个红球，蓝球按顺序出现）

        每次生成16注或16的倍数，每期必中一个蓝球，最少中5元，花费 27元

SQL代码

-- 随机生成红球，蓝球按顺序生成
create table #BicolorBallRandom(
	id int IDENTITY
	, room varchar(20)
	, roomNum int
	, color varchar(20)
	, colorNum int
	, Red DECIMAL (38, 10)
)

create table #BicolorBallRandomResult(
	id int IDENTITY
	, room varchar(20)
	, roomNum int
	, color varchar(20)
	, colorNum int
	, Red DECIMAL (38, 10)
)
declare @k int
declare @i int

set @k = 1
set @i = 1
-- 随机生成6个红球，蓝球按顺序生成
while @k <= 16
begin
	while @i< 7
		begin
			declare @red int,@blue int
			select @red = cast( ceiling(rand()*33) as int)	
			if not exists(select 1 from #BicolorBallRandom where Red = @red and roomNum = @k)
				begin 
					insert into #BicolorBallRandom
					select '第'+convert(varchar(20),@k)+'组',@k,'红',@i, @red
					set @i = @i + 1
				end
			else
				begin
					set @i = @i
				end
			if(@i = 7)
				begin
					if((@k % 16) = 0)
					begin
						insert into #BicolorBallRandom
						select '第'+convert(varchar(20),@k)+'组',@k,'蓝',7, 16
					end
					else
					begin
						insert into #BicolorBallRandom
						select '第'+convert(varchar(20),@k)+'组',@k,'蓝',7, @k % 16
					end
				end
		end
	set @i = 1
	set @k = @k + 1
	
end

-- 展示，红球按大小顺序排序
insert into #BicolorBallRandomResult
select room	,roomNum	,color	,(ROW_NUMBER() OVER(PARTITION BY roomNum ORDER BY roomNum,color,Red)) colorNum	,Red from #BicolorBallRandom order by roomNum,color,Red

select
sum(roomNum) / 7  id
,room
,sum(Red1) 红1
,sum(Red2) 红2
,sum(Red3) 红3
,sum(Red4) 红4
,sum(Red5) 红5
,sum(Red6) 红6
,sum(Blue) 蓝

from (
	select 
	room
	,roomNum
	,case when colorNum = 1 then Red else 0 end  Red1
	,case when colorNum = 2 then Red else 0 end  Red2
	,case when colorNum = 3 then Red else 0 end  Red3
	,case when colorNum = 4 then Red else 0 end  Red4
	,case when colorNum = 5 then Red else 0 end  Red5
	,case when colorNum = 6 then Red else 0 end  Red6
	,case when colorNum = 7 then Red else 0 end  Blue
	from #BicolorBallRandomResult 
)a
group by room,roomNum
order by roomNum
-- 删除临时表
drop table #BicolorBallRandom
drop table #BicolorBallRandomResult

成果展示