查询OCM最终排名

将返回其最终排名（按车辆），并携带其 carId 所属的 userId
并按最终车辆的成绩进行从大到小的排列
需自定义：

• T."competitionId"
• RANK IN (?, ?, ?, ?, ?)

WITH RANKED AS (
		SELECT ROW_NUMBER() OVER (ORDER BY T.RESULT DESC) AS RANK, T."dbId", T."carId", T."competitionId"
			, T."periodId", T.RESULT, T."tunePower", T."tuneHandling", C."userId"
		FROM PUBLIC."OCMTally" T
			LEFT JOIN PUBLIC."Car" C ON T."carId" = C."carId"
		WHERE T."competitionId" = 8
	)
SELECT *
FROM RANKED
WHERE RANK IN (1, 2, 3, 50, 75); 

查询OCM参与玩家

将返回查询的 competitionId 的参与玩家情况
将自动排除重复项，并按最终车辆的成绩进行从大到小的排列
需自定义：

• competition_id

WITH params AS (
		SELECT 8::int AS competition_id 
	), 
	ranked AS (
		SELECT t."dbId", t."carId", t."competitionId", t."periodId", t.result
			, t."tunePower", t."tuneHandling", c."userId", ROW_NUMBER() OVER (PARTITION BY c."userId" ORDER BY t.result DESC) AS rn
		FROM public."OCMTally" t
			JOIN public."Car" c ON t."carId" = c."carId"
			JOIN params p ON t."competitionId" = p.competition_id
		WHERE c."userId" <> 999999999
	)
SELECT "dbId", "carId", "competitionId", "periodId", result
	, "tunePower", "tuneHandling", "userId"
FROM ranked
WHERE rn = 1
ORDER BY result DESC;

快速发放奖励

Lottery Ticket（ALL）

UPDATE public."WebUser"
	SET "lotteryTicket" = "lotteryTicket" + 10
	WHERE "userId" IN(
		-- 这里填用户ID的数组
	);

Extra Prize

WITH params(rank, u_rank) AS (
    VALUES
        (1, 10001), -- 排名第一的用户ID
        (2, 10002), -- 排名第二的用户ID
        (3, 10003), -- 排名第三的用户ID
        (50, 10004), -- 排名第五十的用户ID
        (75, 10005) -- 排名第七十五的用户ID
)
UPDATE public."WebUser" u
SET 
    "lotteryTicket" = "lotteryTicket" + CASE p.rank
        -- 这里是每个等级的奖励（抽奖券）
        WHEN 1 THEN 30
        WHEN 2 THEN 25
        WHEN 3 THEN 20
        WHEN 50 THEN 15
        WHEN 75 THEN 10
        ELSE 0
    END,
    "xrCredit" = "xrCredit" + CASE p.rank
        -- 这里是每个等级的奖励（点数）
        WHEN 1 THEN 10000
        WHEN 2 THEN 8000
        WHEN 3 THEN 6000
        WHEN 50 THEN 5000
        WHEN 75 THEN 4000
        ELSE 0
    END
FROM params p
WHERE u."userId" = p.u_rank;