[SQL Server] gacha ガチャ・くじ引き関連SQL

inno
2022-09-07 10:28 616 0
gacha ガチャ・くじ引き関連SQL.
以下のSQLを一括で実行する。


CREATE TABLE #tGachaItem
(
ItemID varchar(50)
,ItemRate float
)

INSERT INTO #tGachaItem (ItemID, ItemRate) VALUES ('はずれ', 76)
INSERT INTO #tGachaItem (ItemID, ItemRate) VALUES ('3等 : アイテム', 15)
INSERT INTO #tGachaItem (ItemID, ItemRate) VALUES ('2等 : アイテム', 10)
INSERT INTO #tGachaItem (ItemID, ItemRate) VALUES ('1等 : アイテム', 1)


DECLARE @RateSum float
SELECT @RateSum = SUM(ItemRate) FROM #tGachaItem WITH (NOLOCK)

DECLARE @Rand float
SELECT @Rand = CONVERT(float,RAND() * @RateSum)

CREATE TABLE #ItemRate 
(
Idx int identity(1,1)
,ItemID varchar(50)
,ItemRate float
)

INSERT INTO #ItemRate
SELECT ItemID, ItemRate
FROM #tGachaItem WITH (NOLOCK)
ORDER BY ItemRate, ItemID

DECLARE @ItemID varchar(50)

SELECT TOP 1 @ItemID = A.ItemID
FROM #ItemRate A WITH (NOLOCK)
INNER JOIN #ItemRate B WITH (NOLOCK) 
ON A.Idx >= B.Idx
GROUP BY A.ItemID,A.ItemRate
HAVING SUM(B.ItemRate) >= @Rand 
ORDER BY A.ItemRate

SELECT @ItemID AS ItemID

DROP TABLE #ItemRate

コメント