MySQL关联inventory与parent_items表并按合并单元排序实现

场景1：分类列表仅展示父商品（作为单元）和独立商品 #

如果你的分类导航页只需要展示父商品（每个父商品仅显示一次）和独立商品，并按名称混合字母排序，可使用以下查询：

-- 合并独立商品与去重后的父商品，实现混合排序与分页
SELECT 
    i.Item AS display_name,
    i.System_ID AS id,
    'independent' AS item_type
FROM inventory i
WHERE i.Parent_ID IS NULL
  AND i.category = '书籍与音乐' -- 替换为你的分类过滤条件
UNION
SELECT 
    pi.Item AS display_name,
    pi.Parent_ID AS id,
    'parent' AS item_type
FROM parent_items pi
INNER JOIN inventory i ON pi.Parent_ID = i.Parent_ID
WHERE i.category = '书籍与音乐'
GROUP BY pi.Parent_ID, pi.Item -- 确保同一父商品仅出现一次
ORDER BY display_name
LIMIT 10 OFFSET 0; -- LIMIT为每页条数，OFFSET为页码对应的偏移量（例如第2页OFFSET=10）

关键说明：

• 第一部分查询inventory表中无Parent_ID的独立商品，作为独立单元展示
• 第二部分通过关联inventory筛选出目标分类下的父商品，用GROUP BY去重，避免同一父商品重复出现
• UNION合并两个结果集，ORDER BY display_name实现所有单元按字母顺序混合排序
• LIMIT和OFFSET实现分页，确保分页逻辑正确，无商品重复或遗漏

场景2：分类列表展示所有商品（含子商品），但子商品跟随父商品排序 #

如果需要在分类列表中展示所有库存商品（包括子商品），但子商品需按父商品名称排序（而非自身名称），可使用以下查询：

SELECT 
    -- 子商品显示为「父商品名 - 子商品名」，独立商品显示自身名称
    CASE 
        WHEN pi.Item IS NOT NULL THEN CONCAT(pi.Item, ' - ', i.Item)
        ELSE i.Item 
    END AS display_name,
    -- 排序优先级：有父商品则用父商品名称，否则用商品自身名称
    COALESCE(pi.Item, i.Item) AS sort_key,
    i.System_ID AS item_id,
    pi.Parent_ID AS parent_id
FROM inventory i
LEFT JOIN parent_items pi ON i.Parent_ID = pi.Parent_ID
WHERE i.category = '书籍与音乐' -- 替换为你的分类过滤条件
-- 先按父/商品名称排序，同组子商品再按自身名称排序
ORDER BY sort_key, display_name
LIMIT 10 OFFSET 0;

关键说明：

• 使用COALESCE(pi.Item, i.Item)作为排序字段，确保子商品跟随父商品的字母顺序排列（比如父商品名为“A开头”，子商品即使是“B开头”也会排在A区域）
• 子商品的显示名称拼接了父商品名，方便用户识别归属
• 排序时先按sort_key分组，同组内的子商品再按自身名称排序，保证同一父商品下的子商品有序排列

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