如何编写PostgreSQL查询：筛选NPC的首个可用任务（排除已有任务）

需求说明 #

编写PostgreSQL查询，实现以下逻辑：

• 筛选出每个NPC的首个可用任务（依据任务ID最小判定）
• 若用户已接受该NPC的任务（即任务处于进行中，quest_completions表中对应记录rewarded为false），则不返回该NPC

表结构及测试数据 #

Quests表 #

id | npc     | name
---+---------+------------
 1 | NPC-1   | Kill an Ogre 
 2 | NPC-1   | Kill a Bear
 3 | NPC-2   | Kill a Cow

quest_completions表说明及数据 #

• 表中存在记录代表任务正在进行，除非rewarded设为true（此时任务已完成领奖，可接下一个任务）
• 测试数据：

quest_name   |  rewarded   |  player_id
-------------+-------------+-----------
Kill an Ogre |   false     |      0

预期结果 #

返回结果为[NPC-2]，因为这是唯一有可用任务的NPC：

• NPC-1的首个任务Kill an Ogre正被玩家0接受（rewarded=false），因此该NPC无可用任务
• NPC-2的首个任务Kill a Cow未被玩家接受，因此符合条件

原查询问题 #

用户编写的查询无法正常运行，原语句如下：

SELECT 
    npc
FROM 
    (SELECT DISTINCT 
         ON(npc) quests.npc,
         MIN(quests.id),
         quests.name
     FROM 
         quests
     LEFT JOIN 
         quest_completions ON quest_completions.quest_name = quests.name
      WHERE 
          (quest_completions.rewarded IS NULL
           OR quest_completions.rewarded = false)
          AND quest_completions.player_id = 5
      GROUP BY 
          npc, quests.name) a
WHERE 
    a.name NOT IN (SELECT quest_completions.quest_name
                   FROM quest_completions
                   LEFT JOIN quests ON quest_completions.quest_name = quests.name
                   WHERE rewarded = false);

原查询存在的问题 #

1. LEFT JOIN后添加quest_completions.player_id = 5条件，会将无关联记录的Quest过滤掉，违背左连接的初衷
2. DISTINCT ON(npc)和MIN(quests.id)结合使用逻辑混乱，DISTINCT ON已按npc取首条记录，无需再用聚合函数
3. 子查询的过滤条件逻辑错误，未能正确区分任务是否处于进行中
4. 外层NOT IN子查询未指定玩家ID，会过滤所有处于进行中的任务，而非当前玩家的任务

正确查询语句 #

WITH npc_first_quest AS (
    -- 获取每个NPC的首个任务（最小ID）
    SELECT DISTINCT ON(npc)
        npc,
        name AS first_quest_name
    FROM quests
    ORDER BY npc, id ASC
),
player_active_quests AS (
    -- 获取当前玩家（示例中player_id=0）正在进行的任务
    SELECT quest_name
    FROM quest_completions
    WHERE player_id = 0
      AND rewarded = false
)
SELECT nfq.npc
FROM npc_first_quest nfq
LEFT JOIN player_active_quests paq
    ON nfq.first_quest_name = paq.quest_name
-- 过滤掉首个任务正在进行的NPC
WHERE paq.quest_name IS NULL;

逻辑说明 #

1. npc_first_quest CTE：使用DISTINCT ON(npc)结合ORDER BY npc, id ASC，精准获取每个NPC的首个任务（ID最小的任务）
2. player_active_quests CTE：筛选出指定玩家正在进行的任务（rewarded=false）
3. 最后将两个CTE左连接，保留首个任务不在玩家进行中任务列表里的NPC，即为符合条件的结果

内容的提问来源于stack exchange，提问作者bezzoon