SQL Server 2014 - 模拟和 CTE 循环

Question

我目前正在尝试创建两个玩家玩骰子的模拟。从代码中可以看出，每位玩家出2个骰子，连续3次(共6个ID)。我想添加另外两列:

1) “结果”栏:显示三场比赛中每场比赛谁赢“赢”或输“输”。我尝试使用连接，但我无法做到。如果“Sum”列中的金额大于第二行中的金额，则第一行应显示“Win”，然后应显示“Loss”

2) “Match”栏:显示模拟的具体数字。例如，前两行将等于“Simulation 1”，第三和第四行将等于“Simulation 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!

Answer 1

这可能就是您所需要的。您可能会发现使用 sqlserver 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