sql - pgsql DELETE + LIMIT + JOIN + ORDER

Question

我正在使用 PostgreSQL，我有一个游戏，玩家可以参加比赛。

我有下表来描述玩家和锦标赛玩家（参加给定锦标赛的玩家）。这些表被简化为仅包含查询中使用的内容：

CREATE TABLE player (
    id INTEGER NOT NULL,
    username VARCHAR(255),
    push_notification_token VARCHAR(255)
);

CREATE TABLE tournament_player (
    tournament_id INTEGER NOT NULL,
    player_id INTEGER NOT NULL,
    victories INTEGER NOT NULL
);

当锦标赛结束时，我想以 100 人为一组删除所有锦标赛玩家，然后我还希望按照他们在锦标赛中的排名（胜利 DESC）进行排序。

我目前正在使用以下查询来删除、限制和加入：

DELETE FROM tournament_player tp 
    USING player p
    WHERE tp.player_id = p.id 
        AND (tp.tournament_id, tp.player_id) IN (
            SELECT tournament_id, player_id 
            FROM tournament_player
        LIMIT 100)
RETURNING tp.player_id, tp.victories, 
    p.username, p.push_notification_token;

我有两个问题：

• 我如何也通过锦标赛玩家（胜利）订购它（DESC）
• 这个查询是浪费资源还是优化得足够好？（我不是说将 INDEX 添加到字段中）

标签： sqlpostgresql

Answer 1

您可以order by tp.victories desc在之前添加您的内部查询，limit以获得您想要的结果。

DELETE FROM tournament_player tp 
    USING player p
    WHERE tp.player_id = p.id 
        AND (tp.tournament_id, tp.player_id) IN (
            SELECT tournament_id, player_id 
            FROM tournament_player
            order by tp.victories desc
        LIMIT 100)
RETURNING tp.player_id, tp.victories, 
    p.username, p.push_notification_token;

而不是IN你也可以使用exits如下：

DELETE FROM tournament_player tp 
    USING player p
    WHERE tp.player_id = p.id 
        AND exists
        (
            SELECT 1 
            FROM tournament_player tpr
            where tpr.tournament_id=tp.tournament_id and tpr.player_id=tp.player_id
            order by tp.victories desc
            LIMIT 100
        )
    RETURNING tp.player_id, tp.victories, 
    p.username, p.push_notification_token;

下面的部分真的有必要吗？

 USING player p
    WHERE tp.player_id = p.id 

如果您可以删除此加入，您的查询将更快。我假设所有的玩家tournament_player都在player桌子上。所以，这个连接是没有必要的。