4表INNER JOIN后如何查询每个prod_name对应的最低价格记录

业务查询共关联4张数据表：

• product_list
• product
• product_img
• pricelist

现有全量关联查询可返回所有表关联后的完整结果，需要调整SQL实现：仅返回每个prod_name对应最低价格的单条记录，例如Toyota Agya仅返回价格155500000的记录、Toyota Calya仅返回价格151600000的记录。

原有全量查询SQL如下：

SELECT
  product_list.id,
  product_list.class,
  product.prod_name,
  product.prod_url,
  product.prod_overview,
  product_img.list_prod340x340,
  pricelist.price 
FROM 
(
  (
    (
      product_list 
      INNER JOIN product ON product_list.id = product.prod_list_id
    ) 
    INNER JOIN product_img ON product.id = product_img.prod_id
  ) 
  INNER JOIN pricelist ON product.id = pricelist.prod_id
)
ORDER BY product_list.id, pricelist.price ASC

提供两种常用实现方式，可根据使用的数据库版本选择：

方案1：通用聚合关联写法（兼容所有数据库版本） #

核心逻辑是先通过子查询按prod_name分组计算出每个产品的最低价格，再和原关联查询做匹配，过滤出价格等于对应产品最低价的记录：

SELECT
  pl.id,
  pl.class,
  p.prod_name,
  p.prod_url,
  p.prod_overview,
  pi.list_prod340x340,
  pr.price 
FROM product_list pl
INNER JOIN product p ON pl.id = p.prod_list_id
INNER JOIN product_img pi ON p.id = pi.prod_id
INNER JOIN pricelist pr ON p.id = pr.prod_id
INNER JOIN (
  SELECT p.prod_name, MIN(pr.price) AS min_price
  FROM product p
  INNER JOIN pricelist pr ON p.id = pr.prod_id
  GROUP BY p.prod_name
) AS min_price_map 
  ON p.prod_name = min_price_map.prod_name 
  AND pr.price = min_price_map.min_price
ORDER BY pl.id, pr.price ASC;

方案2：窗口函数写法（支持MySQL 8.0+、PostgreSQL、SQL Server等主流新版本数据库） #

用窗口函数按prod_name分区、按价格升序打行号，每个分区只取行号为1的（即最低价）记录即可，逻辑更简洁，还能自动处理同产品多条同价记录的去重问题：

SELECT
  id,
  class,
  prod_name,
  prod_url,
  prod_overview,
  list_prod340x340,
  price
FROM (
  SELECT
    pl.id,
    pl.class,
    p.prod_name,
    p.prod_url,
    p.prod_overview,
    pi.list_prod340x340,
    pr.price,
    ROW_NUMBER() OVER (PARTITION BY p.prod_name ORDER BY pr.price ASC) AS price_rank
  FROM product_list pl
  INNER JOIN product p ON pl.id = p.prod_list_id
  INNER JOIN product_img pi ON p.id = pi.prod_id
  INNER JOIN pricelist pr ON p.id = pr.prod_id
) AS ranked_result
WHERE price_rank = 1
ORDER BY id, price ASC;

注意：如果业务上需要保留同一个产品下所有价格等于最低价的记录，可以把窗口函数里的ROW_NUMBER()替换为RANK()；如果只需要任意取一条最低价记录，保留ROW_NUMBER()即可。

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