goal个 抽第一名到第五名的入场券.一个用户不可能赢得多个位置.如果用户没有下单,那么他们的所有门票都必须在抽奖时考虑在内.
progress个 我能抽出一个获胜者,但不能全部抽到五个.
IDea个 我可以再重复查询4次,每次删除前面的USER_ID.但肯定有更好的办法.
question个 我怎么才能把5张票都抽出来呢?
tickets table个
- ID
- user_ID
- 金额
- 创建于
this is my query个
WITH gen_tickets AS (
-- Use `generate_series()` to create a row for each ticket
SELECT user_ID, 金额, 创建于
FROM tickets
CROSS JOIN LATERAL generate_series(1, CAST(金额 AS INTEGER))
),
shuffle AS (
SELECT user_ID, 金额, row_number() OVER (ORDER BY random()) AS rn
FROM gen_tickets
)
SELECT * FROM shuffle
ORDER BY RANDOM()
LIMIT 1;