gacha ガチャ・くじ引き関連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