mysql - 如何根据您拥有的票数随机选择一行
问题描述
我有这张桌子叫my_users
my_id | name | raffle_tickets
1 | Bob | 3
2 | Sam | 59
3 | Bill | 0
4 | Jane | 10
5 | Mike | 12
如您所见,他Sam
有59
门票,因此他获胜的机会最高。中奖几率:
Sam
=59/74
Bob
=3/74
Jane
=10/74
Bill
=0/74
Mike
=12/74
PS:74
是表中的总票数(只是让你知道我没有随机挑选74
)
在此基础上,如何随机抽奖,但又能保证抽奖券多的人中奖概率高?然后被选中的获胜者,从他们的总票中扣除 1 张票
UPDATE my_users
SET raffle_tickets = raffle_tickets - 1
WHERE my_id = --- Then I get stuck here...
服务器版本:5.7.30
解决方案
对于 MySQL 8+
WITH
cte1 AS ( SELECT name, SUM(raffle_tickets) OVER (ORDER BY my_id) cum_sum
FROM my_users ),
cte2 AS ( SELECT SUM(raffle_tickets) * RAND() random_sum
FROM my_users )
SELECT name
FROM cte1
CROSS JOIN cte2
WHERE cum_sum >= random_sum
ORDER BY cum_sum LIMIT 1;
5岁以上
SELECT cte1.name
FROM ( SELECT t2.my_id id, t2.name, SUM(t1.raffle_tickets) cum_sum
FROM my_users t1
JOIN my_users t2 ON t1.my_id <= t2.my_id
WHERE t1.raffle_tickets > 0
GROUP BY t2.my_id, t2.name ) cte1
CROSS JOIN ( SELECT RAND() * SUM(raffle_tickets) random_sum
FROM my_users ) cte2
WHERE cte1.cum_sum >= cte2.random_sum
ORDER BY cte1.cum_sum LIMIT 1;