门店商品价格查询：能否用SQL语句实现，还是需用表函数？

一、修正查询语句 #

你的查询失效的核心原因：LEFT JOIN LocalPrices 后用 INNER JOIN Products 关联 lp.productId，当门店无专属价格记录时，lp.productId 为 NULL，INNER JOIN 会直接过滤掉这些无匹配的行，导致拿不到该门店使用默认价格的商品。

以下是两种可行的修正方案：

方案1：关联所有商品 + 左连本地价格（推荐） #

通过 CROSS JOIN 先获取目标门店与所有商品的组合，再左连本地价格表，最后用 COALESCE 优先取本地价，无本地价则用商品默认价：

SELECT 
  loc.locationId,
  prod.productId,
  prod.productName,
  COALESCE(lp.localPrice, prod.defaultPrice) AS finalPrice
FROM Location loc
CROSS JOIN Products prod
LEFT JOIN LocalPrices lp 
  ON loc.locationId = lp.locationId 
  AND prod.productId = lp.productId
WHERE loc.locationId = <目标门店ID>

方案2：从商品表出发关联 #

如果更习惯从商品表开始查询，可调整连接顺序，同样保证能拿到所有商品的价格：

SELECT 
  loc.locationId,
  prod.productId,
  prod.productName,
  COALESCE(lp.localPrice, prod.defaultPrice) AS finalPrice
FROM Products prod
CROSS JOIN Location loc
LEFT JOIN LocalPrices lp 
  ON loc.locationId = lp.locationId 
  AND prod.productId = lp.productId
WHERE loc.locationId = <目标门店ID>

二、表结构优化建议 #

你的现有表结构已经符合业务逻辑（默认价+覆盖价的分离存储），可以做以下优化提升性能和数据可靠性：

• 给 LocalPrices 表设置联合主键（locationId, productId），避免同一门店同一商品出现多条重复价格记录，保证数据唯一性。
• 为 LocalPrices 的 locationId 和 productId 字段建立联合索引，大幅提升表连接时的查询效率。
• 如果实际业务中并非所有门店售卖全部商品，可新增一张 LocationProducts 中间表，存储各门店可售卖的商品列表，替代查询中的 CROSS JOIN，避免全量商品关联带来的性能损耗。

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