查询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;

查询VSORG最终排名

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

• g."ghostExpeditionId"
• LIMIT

SELECT 
    g."dbId",
    g."carId",
    c."userId",
    g."ghostExpeditionId",
    g."sugorokuPoint",
    g."earnedScore",
    g."score"
FROM public."GhostExpedition" AS g
JOIN public."Car" AS c
    ON g."carId" = c."carId"
WHERE 
    g."ghostExpeditionId" = ?
    AND c."userId" <> 999999999
ORDER BY 
    g."score" DESC
LIMIT ?;

查询VSORG参与玩家

将返回其最终参与情况，并携带其 carId 所属的 userId
每个 userId 只会有其 carId 中最大的 score 的项
并按最终的成绩进行从大到小的排列
需自定义：

• g."ghostExpeditionId"

SELECT 
    ROW_NUMBER() OVER (ORDER BY t."score" DESC) AS rank,
    t.*
FROM (
    SELECT DISTINCT ON (c."userId")
        g."dbId",
        g."carId",
        c."userId",
        g."ghostExpeditionId",
        g."sugorokuPoint",
        g."earnedScore",
        g."score"
    FROM public."GhostExpedition" AS g
    JOIN public."Car" AS c
        ON g."carId" = c."carId"
    WHERE 
        g."ghostExpeditionId" = ?
        AND c."userId" <> 999999999
    ORDER BY 
        c."userId",
        g."score" DESC
) AS t
ORDER BY 
    t."score" 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;