运行总计，直到特定条件为真为止
我有张桌子代表经销商的名片和他们的军衔。我现在正在尝试进行一个查询(尽可能快)来设置游戏状态。
(As said before, only the dealer cards is shown)
W = Win
S = Stand
L = Loss
B = Blackjack (in two cards)关于规则:经销商在21岁的时候就赢了，如果是两张牌的话。如果等级介于17到20之间，则为S= stand。超过21岁是一种损失。
职级：
1 (ACE) -1或11级。计算为11。 2-10 - 2-10级 11-13 (骑士王)- 10级
╔════╦══════╦════════╗
║ Id ║ Rank ║ Status ║
╠════╬══════╬════════╣
║ 1 ║ 1 ║ ║
║ 2 ║ 5 ║ ║
║ 3 ║ 8 ║ L ║ //24 = Loss
║ 4 ║ 3 ║ ║
║ 5 ║ 1 ║ ║
║ 6 ║ 7 ║ W ║ //21 = Win
║ 7 ║ 10 ║ ║
║ 8 ║ 1 ║ B ║ //21 = Blackjack
║ 9 ║ 10 ║ ║
╚════╩══════╩════════╝我试着用一个计数器来检查它是否是21点，然后我用一个"RunningPoint“来检查卡片的总和。
我现在有了一个糟糕的解决方案，当它包含大量数据时，它显示出非常糟糕的性能。您将如何做到这一点，以及如何优化查询？当使用更多的数据时，我还需要使用选项( more递归0)。
(当有100万行时，甚至不可能运行这个.)
我的例子：http://sqlfiddle.com/#!6/3855e/1
回答 2
Stack Overflow用户
发布于 2015-10-12 08:59:19
没有使用普通SQL (包括窗口聚合函数)的有效解决方案，至少没有人找到一个解决方案，但是:-)
递归查询执行不好，因为它太复杂了，这是一个简化的版本：
编辑：修正计算(小提琴)
WITH ctePoints AS
(
SELECT 1 AS id
,rank
,CASE
WHEN rank >= 10 THEN 10
WHEN rank = 1 THEN 11
ELSE rank
END AS Point
,1 AS Counter
FROM dbo.BlackJack
WHERE Id = 1
UNION ALL
SELECT t2.Id
,t2.rank
,CASE WHEN t1.Point < 17 THEN t1.Point ELSE 0 END
+ CASE
WHEN t2.rank >= 10 THEN 10
WHEN t2.rank = 1 THEN 11
ELSE t2.rank
END AS Point
,CASE WHEN t1.Point < 17 THEN t1.Counter + 1 ELSE 1 END AS Counter
FROM dbo.BlackJack AS t2
INNER JOIN ctePoints AS t1 ON t2.Id = t1.Id + 1
)
SELECT ctepoints.*
,CASE
WHEN Point < 17 THEN ''
WHEN Point < 20 THEN 'S'
WHEN Point > 21 THEN 'L'
WHEN Point = 21 AND Counter = 2 THEN 'B'
ELSE 'W'
END AS DealerStatus
FROM ctePoints它可能仍然太慢，因为它逐行处理。
我通常使用递归SQL替换游标逻辑(因为在我的DBMS中，它通常要快得多)，但是游标更新实际上可能更快(演示)：
CREATE TABLE #BlackJack
(
id INT PRIMARY KEY CLUSTERED
,Rank INT
,DealerStatus CHAR(1)
);
insert into #BlackJack (Id, Rank)
values
(1, 1),(2, 5), (3, 8), (4, 3), (5, 1), (6, 7), (7, 10), (8, 1),(9, 10), (10, 10), (11,1);
DECLARE @Counter INT = 0
,@Point INT = 0
,@id int
,@Rank int
,@DealerStatus char(1)
DECLARE c CURSOR
FOR
SELECT id, Rank
FROM #BlackJack
ORDER BY id FOR UPDATE OF DealerStatus
OPEN c
FETCH NEXT FROM c INTO @id, @Rank
WHILE @@FETCH_STATUS = 0
BEGIN
SET @counter = @counter + 1
SET @Rank = CASE
WHEN @Rank >= 10 THEN 10
WHEN @Rank = 1 THEN 11
ELSE @Rank
END
SET @Point = @Point + @Rank
SET @DealerStatus = CASE
WHEN @Point < 17 THEN ''
WHEN @Point < 20 THEN 'S'
WHEN @Point > 21 THEN 'L'
WHEN @Point = 21 AND @Counter = 2 THEN 'B'
ELSE 'W'
END
IF @Point >= 17
BEGIN
UPDATE #BlackJack
SET DealerStatus = @DealerStatus
WHERE CURRENT OF c;
SET @Point = 0
SET @Counter = 0
END
FETCH NEXT FROM c INTO @id, @Rank
END
CLOSE c
DEALLOCATE c
SELECT * FROM #BlackJack ORDER BY id仍然@lad2025的“古怪更新”是获得预期结果的最快方法，但是它使用的是一个无文档的特性，如果一个Service Pack破坏了它，就没有办法抱怨它:-)
Stack Overflow用户
发布于 2015-10-11 23:58:02
此解决方案基于古怪的更新。更多信息，这里。
数据和结构：
CREATE TABLE #BlackJack
(
id INT
,Rank INT
,running_total INT
,result NVARCHAR(100)
);
CREATE CLUSTERED INDEX IX_ROW_NUM ON #BlackJack(id);
insert into #BlackJack (Id, Rank)
values (1, 1),(2, 5), (3, 8), (4, 3), (5, 1),
(6, 7), (7, 10), (8, 1),(9, 10), (10, 10), (11,1);主要查询：
DECLARE @running_total INT = 0
,@number_of_cards INT = 0
,@prev_running_total INT = 0;
UPDATE #BlackJack
SET
@prev_running_total = @running_total
,@running_total = running_total = IIF(@running_total >= 20, 0, @running_total)
+ CHOOSE(Rank,11,2,3,4,5,6,7,8,9,10,10,10,10)
,result = CASE WHEN @running_total = 20 THEN 'S'
WHEN @running_total = 21 AND @number_of_cards = 2 THEN 'B'
WHEN @running_total = 21 THEN 'W'
WHEN @running_total > 21 THEN 'L'
ELSE NULL
END
,@number_of_cards = IIF(@prev_running_total >= 20, 0, @number_of_cards) + 1
FROM #BlackJack WITH(INDEX(IX_ROW_NUM))
OPTION (MAXDOP 1);
SELECT *
FROM #BlackJack
ORDER BY id;警告
如果使用SQL Server < 2012，则需要将IIF和CHOOSE替换为CASE。我不检查所有的Blackjack规则，只为提供的样本。如果有问题，可以随意更改CASE逻辑。
其次，我用辅助列扩展了基表BlackJack，但如果需要，您可以创建任何新表。
关键是基于集群密钥上升顺序读取数据，不允许并行执行。在生产中使用它之前，请检查它如何使用大型数据集。
https://stackoverflow.com/questions/33069463
复制相似问题
腾讯云开发者
