SQL Server 2014 - 模拟和CTE循环

Question

我正在尝试创建一个两个玩家掷骰子的模拟。从代码中可以看出，每个玩家会掷2颗骰子，连续进行三次（总计6个ID）。我想要添加两列：

1) “结果”列：显示每场比赛的胜负情况“胜利”或“失败”。我尝试使用联接，但没能成功。第一行应该显示“胜利”，如果在“总和”列中的金额大于第二行中的金额，则第二行应显示“失败”。

2) “比赛”列：显示模拟的具体编号。例如，前两行将等于“模拟1”，第三行和第四行将等于“模拟2”。由于行数有限，我可以使用“case when”来实现，但我希望有更动态的方式，在我决定增加模拟数量时也适用。

    with cte as
(
select 1 Id,
1.0 + floor(6 * RAND(convert(varbinary, newid()))) Die1,
1.0 + floor(6 * RAND(convert(varbinary, newid()))) Die2
union  all
select Id + 1,  
1.0 + floor(6 * RAND(convert(varbinary, newid()))) Die1,
1.0 + floor(6 * RAND(convert(varbinary, newid()))) Die2
from cte
where 
    id < 6
), 
cte2 as
(
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
from cte a
)
Select a.*
from cte2 a

OPTION(MAXRECURSION 0)

非常抱歉，我还在学习SQL!

- riversus

---

预期结果将有所帮助。 - Dmitrij Kultasev

我在最后添加了一张图片，展示了期望的结果。 - riversus

我不理解你想要的结果。你是想要6行，每行有3个结果吗？ - t-clausen.dk

@riversus，请保存我添加的图片，以便每个人都可以看到。基本上有3个“游戏”或“比赛”，OP想要展示每个“游戏/比赛”的获胜者。我在每个游戏/比赛之间画了红线。 - SS_DBA

@t-clausen.dk 你能看到这张图片吗？ - riversus

个人而言，我会创建一些表来存储数据。这样它就可以像您想要的那样动态了。如果您像这样动态生成所有数据，您将很难使其变得动态。 - Sean Lange

Answer 1

这可能是你需要的内容。你可以使用SQL Server 2012或更新版本来找到更易读的方法：

DECLARE @matches INT = 3

;WITH N1(N)AS(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))n(N)),
N2(N)AS(SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3(N)AS(SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
N4(ID)AS(SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N3 AS N1 CROSS JOIN N3 AS N2)
, CTE as
(
  SELECT
    id,
    1 + floor(6 * rand(checksum(NEWID()))) Die1,
    1 + floor(6 * rand(checksum(NEWID()))) Die2
  FROM n4
  WHERE id <= @matches * 2
)
SELECT 
  *,
  Die1+Die2 as Sum,
  CASE WHEN id%2=1 then 1
  ELSE 2 end as Player, 
  Results = 
    CASE 
      sign(sum(CASE WHEN id %2 = 0 THEN - die1 - die2 ELSE die1 + die2 END)
        over (partition by (id- 1)/2)) * (id%2 * 2 - 1)
    WHEN -1 THEN 'Loss' WHEN 0 THEN 'Draw' WHEN 1 THEN 'Win' END,
  Match = (ID + 1) / 2
FROM CTE

- t-clausen.dk

---

完全正确。您是否可以添加一些注释来解释查询？只有在您愿意的情况下。这将使我更容易完全理解。 - riversus

比我的更简单 :) 写得很好 - SqlKindaGuy

Answer 2

这不是最美观的答案，但这是我在得到 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

- SqlKindaGuy

---

提前说明 - 抱歉滥用游标，但它可以在不到一秒的时间内执行1000行。 - SqlKindaGuy

Answer 3

在SQL Server中更清晰和更快的方法是使用序列表，而不是递归CTE。类似这样的东西：

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 (100000) 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
from outcome a

- David Browne - Microsoft

---

很酷，运行速度超快，但仍然无法解决OP的问题。 :-) - SS_DBA