这不是最美观的答案,但这是我在得到 David Browne 的帮助后想出来的:
表格
CREATE TABLE [dbo].[dices](
[id] [int] NULL,
[die1] [int] NULL,
[die2] [int] NULL,
[sum] [int] NULL,
[Player] [int] NULL,
[Match] [int] NULL
) ON [PRIMARY]
SQL 代码
with thousandRows as
(
select 0 i
from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(i)
cross join (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(i)
cross join (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(i)
), iterations as
(
select top (14) row_number() over (order by (select null)) i
from thousandRows t0, thousandRows t1
), outcome as
(
select i id,
1.0 + floor(6 * RAND(convert(varbinary, newid()))) Die1,
1.0 + floor(6 * RAND(convert(varbinary, newid()))) Die2
from iterations
)
select *,a.Die1+a.Die2 as Sum,
case when a.id=0 then 'Null'
when a.id%2=1 then '1'
else '2' end as Player
into #temp
from outcome a
;
Truncate table dices;
declare @Id int
declare @die1 int
declare @die2 int
declare @sum int
declare @Player nvarchar(50)
declare @match int = 1
declare @SetMatch int
DECLARE player1Cur CURSOR FOR
SELECT id,die1,die2,[sum],player from #temp
where player = 1 order by id
OPEN player1Cur
FETCH NEXT FROM player1Cur
INTO @Id,@die1,@die2,@sum,@Player
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
SET @SetMatch = @match
insert into dices
select @id,@die1,@die2,@sum,@Player,@SetMatch
SET @match = @match +1
END
FETCH NEXT FROM player1Cur
INTO @Id,@die1,@die2,@sum,@Player
END
CLOSE player1Cur
DEALLOCATE player1Cur
SET @SetMatch = 0
SET @match = 1
DECLARE player2Cur CURSOR FOR
SELECT id,die1,die2,[sum],player from #temp
where player = 2 order by id
OPEN player2Cur
FETCH NEXT FROM player2Cur
INTO @Id,@die1,@die2,@sum,@Player
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
SET @SetMatch = @match
insert into dices
select @id,@die1,@die2,@sum,@Player,@SetMatch
SET @match = @match +1
END
FETCH NEXT FROM player2Cur
INTO @Id,@die1,@die2,@sum,@Player
END
CLOSE player2Cur
DEALLOCATE player2Cur
;
select *,case when Rank() over(partition by match order by [sum] desc) = 1 then 'Win' Else 'Loss' end as WinLoss from dices
order by match,player
;
Drop table #temp
Result
![enter image description here](https://istack.dev59.com/jUdKb.webp)