如何获取每个物品对应的最高优先级模块关联记录

我来帮你搞定这个需求！先明确下核心诉求：每个物品可关联多个模块，模块的优先级由其所属类型决定，我们要找出每个物品关联的模块中，属于最高优先级类型的所有模块——如果同一最高优先级类型下有多个模块关联该物品，这些模块都要保留；如果之后关联了更高优先级类型的模块，就只保留该类型的模块。

先梳理下咱们的表结构 #

Items 物品表

Item_Module 物品-模块关联表

Module 模块表

Type_Priority 类型优先级表

解决方案：两种SQL实现方式 #

方式一：子查询分组求最高优先级（兼容多数SQL版本）

这种写法适合不支持窗口函数的旧版SQL（比如MySQL 5.x）：

SELECT 
    i.id AS item_id,
    i.name AS item_name,
    m.id AS module_id,
    m.name AS module_name
FROM 
    Items i
JOIN 
    Item_Module im ON i.id = im.item_id
JOIN 
    Module m ON im.module_id = m.id
JOIN 
    Type_Priority tp ON m.type = tp.type
WHERE 
    (i.id, tp.priority) IN (
        -- 先算出每个物品关联的模块对应的最高优先级
        SELECT 
            im_sub.item_id,
            MAX(tp_sub.priority) AS max_priority
        FROM 
            Item_Module im_sub
        JOIN 
            Module m_sub ON im_sub.module_id = m_sub.id
        JOIN 
            Type_Priority tp_sub ON m_sub.type = tp_sub.type
        GROUP BY 
            im_sub.item_id
    )
ORDER BY 
    i.id, m.id;

方式二：窗口函数写法（简洁高效，适合现代SQL）

如果你的数据库支持窗口函数（MySQL 8+、PostgreSQL、SQL Server等），推荐这种写法：

WITH Item_Module_Priority AS (
    SELECT 
        i.id AS item_id,
        i.name AS item_name,
        m.id AS module_id,
        m.name AS module_name,
        tp.priority,
        -- 按物品分组，计算该物品的最高优先级
        MAX(tp.priority) OVER (PARTITION BY i.id) AS max_priority
    FROM 
        Items i
    JOIN 
        Item_Module im ON i.id = im.item_id
    JOIN 
        Module m ON im.module_id = m.id
    JOIN 
        Type_Priority tp ON m.type = tp.type
)
SELECT 
    item_id,
    item_name,
    module_id,
    module_name
FROM 
    Item_Module_Priority
WHERE 
    priority = max_priority
ORDER BY 
    item_id, module_id;

结果验证 #

针对当前的表数据，两种写法都会返回你需要的结果：

如果之后给item1关联module4（即Item_Module表添加(1,4)），那么查询结果会只返回module4的记录，符合“只保留最高优先级类型模块”的需求。

备注：内容来源于stack exchange，提问作者AngryEvil