MySQL三表关联生成视图：无seller_item匹配时取item表默认值

业务背景与需求 #

现有业务系统包含三张关联数据表，表结构与示例数据如下：

• 卖家表（seller）：存储卖家基础信息，包含卖家ID、卖家名称字段，示例数据为ID=1对应S1、ID=2对应S2、ID=3对应S3
• 商品表（item）：存储商品基础信息，包含商品ID、商品名称、商品默认价格、商品默认上架状态字段，示例数据为ID=1对应I1、默认价格50；ID=2对应I2、默认价格100
• 卖家-商品关联表（seller_item）：存储卖家维度的商品自定义配置，包含卖家ID、商品ID、卖家自定义售价、卖家维度商品上架状态字段，示例数据为关联记录（卖家ID=1、商品ID=1、价格75）、（卖家ID=2、商品ID=1、价格25）

需要实现的查询/视图逻辑为：

• 输出全量卖家与全量商品的所有配对列表，不得遗漏未配置自定义规则的组合
• 字段取值遵循优先级：若卖家在seller_item表存在对应商品的自定义配置，优先采用关联表的自定义值；若无匹配的关联记录，自动取商品表的默认值

此前尝试用WHERE NOT EXISTS语法实现相似逻辑，仅能适配查询指定卖家ID的场景，无法输出全量卖家与全量商品的完整配对列表。

最终可执行查询语句 #

SELECT
  S.name AS name,
  I.name AS item,
  IF(SI.visible = 0, SI.visible, I.visible) AS visible,
  IF(COALESCE(SI.price, 0) = 0, I.price, SI.price) AS price 
FROM seller S 
JOIN item I 
LEFT JOIN seller_item SI ON S.id = SI.seller_id AND I.id = SI.item_id 
ORDER BY 1, 2

核心实现逻辑 #

• seller表和item表做内连接，直接生成所有卖家、所有商品的全量配对，从数据源头保证没有组合遗漏
• 对seller_item表用左连接，连接条件同时匹配卖家ID、商品ID，没有自定义配置的关联字段会返回NULL，不会破坏前面生成的全量配对结果
• 字段取值判断规则：
  • 上架状态：只要关联表存了非0的自定义上架状态，就用关联表的配置，否则取商品表的默认上架状态
  • 价格：先用COALESCE把关联表里的NULL价格转成0，如果关联表的价格是0或NULL（也就是没有有效自定义配置），就用商品表的默认价格，否则取卖家自定义的售价

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