如何获取products表所有产品并关联product_variants表最低价格变体

我来帮你搞定这个查询需求～你需要获取所有产品的完整信息，同时匹配每个产品价格最低的变体详情，哪怕部分产品还没添加变体也得显示出来对吧？下面给你两种常用的实现方式，你可以根据自己用的数据库版本来选：

先明确数据结构和期望结果 #

示例数据表 #

products表：

id  product_name  photo
1   product_1     1.png
2   product_2     2.png
3   product_3     3.png
4   product_4     4.png

product_variants表：

id  product_id  packet_size  price
1   1           100 ML       50 RS.
2   1           200 ML       100 Rs.
3   1           300 ML       150 RS.
4   2           300 L        300 Rs.
5   2           200 L        200 Rs.
6   3           200 K        200 Rs.

期望输出 #

id  product_name  photo  packet_size  price
1   product_1     1.png  100 ML       50 RS.
2   product_2     2.png  200 L        200 Rs.
3   product_3     3.png  200 K        200 Rs.
4   product_4     4.png  NULL         NULL  -- 无变体的产品显示空值

方法一：子查询+左关联（兼容绝大多数数据库版本） #

这种方法先通过子查询找出每个product_id对应的最低价格，再用左关联把产品表和变体表关联起来，确保所有产品都能被查询到。

SQL代码 #

SELECT 
    p.id,
    p.product_name,
    p.photo,
    pv.packet_size,
    pv.price
FROM products p
LEFT JOIN (
    -- 子查询：获取每个产品的最低变体价格
    SELECT product_id, MIN(price) AS min_price
    FROM product_variants
    GROUP BY product_id
) min_pv ON p.id = min_pv.product_id
-- 关联变体表，匹配对应最低价格的记录
LEFT JOIN product_variants pv 
    ON min_pv.product_id = pv.product_id 
    AND min_pv.min_price = pv.price;

说明 #

• 用LEFT JOIN保证没变体的产品（比如product_4）也会出现在结果里，对应的变体字段显示NULL。
• 如果同一个产品有多个变体价格相同且都是最低，这个查询会返回多条该产品的记录；要是你只想留一条，可以在子查询里加个条件（比如取最小的pv.id）来过滤。

方法二：窗口函数ROW_NUMBER()（适合支持窗口函数的数据库，如MySQL8.0+、PostgreSQL、SQL Server等） #

这种写法更简洁，用窗口函数给每个产品的变体按价格排序，直接取排序为1的那条（也就是价格最低的）。

SQL代码 #

SELECT 
    id,
    product_name,
    photo,
    packet_size,
    price
FROM (
    SELECT 
        p.id,
        p.product_name,
        p.photo,
        pv.packet_size,
        pv.price,
        -- 按产品分组，按价格升序排序，给每条变体标记序号
        ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY pv.price ASC) AS rn
    FROM products p
    LEFT JOIN product_variants pv ON p.id = pv.product_id
) temp
-- 只保留每个产品的第一条（价格最低的变体），以及无变体的产品
WHERE rn = 1 OR rn IS NULL;

说明 #

• PARTITION BY p.id表示按产品分组，ORDER BY pv.price ASC让同一组内的变体按价格从低到高排序，ROW_NUMBER()会给每组的第一条记录标为1。
• 如果同一产品有多个最低价格的变体，这个方法只会返回其中一条；你可以在ORDER BY里加其他字段（比如pv.id ASC）来指定具体取哪一条。

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