如何查询Article表中各颜色下重量最高产品的des_字段

表结构与需求 #

• 表名：Article（对应英文product表）
• 字段：
  • numArticle：主键（PK）
  • poids：产品重量（对应英文weight）
  • des_：产品名称/标识（对应英文Designation）
  • couleur：产品颜色（对应英文color）
• 需求：获取每种颜色下重量最高的产品的des_字段值

原SQL的问题 #

你尝试的SQL语句如下：

SELECT des_
FROM Article
WHERE poids = ANY (SELECT  MAX(poids) 'poidse'
                   FROM Article
                   GROUP BY couleur);

该语句的核心问题是未关联颜色条件：子查询仅返回了各颜色的最大重量集合，但ANY匹配时，只要产品重量属于这个集合，无论它对应哪个颜色都会被选中。比如红色和蓝色的最大重量都是10时，所有重量为10的产品（红、蓝都会被包含），无法实现“每种颜色下重量最高”的精准匹配。

正确解决方案 #

方案1：关联子查询 #

通过子查询针对当前行的颜色，匹配该颜色下的最大重量：

SELECT des_
FROM Article a
WHERE poids = (
    SELECT MAX(poids)
    FROM Article
    WHERE couleur = a.couleur
);

此方法会为每个产品检查其所属颜色的最大重量，精准筛选出各颜色下的最重产品。

方案2：窗口函数（支持同重量多产品场景） #

如果同一种颜色下存在多个重量相同的最重产品，可使用RANK()或ROW_NUMBER()窗口函数灵活处理：

-- 允许同颜色返回多个最重产品（同重量产品都保留）
SELECT des_
FROM (
    SELECT 
        des_,
        RANK() OVER (PARTITION BY couleur ORDER BY poids DESC) AS rank_num
    FROM Article
) t
WHERE rank_num = 1;

-- 每个颜色仅返回一个产品（同重量时按主键排序取第一个）
SELECT des_
FROM (
    SELECT 
        des_,
        ROW_NUMBER() OVER (PARTITION BY couleur ORDER BY poids DESC, numArticle) AS row_num
    FROM Article
) t
WHERE row_num = 1;

PARTITION BY couleur按颜色分组，ORDER BY poids DESC按重量降序排序，rank_num=1或row_num=1即可筛选出每组的目标产品。

方案3：JOIN+聚合子查询 #

先聚合得到各颜色的最大重量，再通过JOIN关联原表匹配对应产品：

SELECT a.des_
FROM Article a
INNER JOIN (
    SELECT couleur, MAX(poids) AS max_poids
    FROM Article
    GROUP BY couleur
) t ON a.couleur = t.couleur AND a.poids = t.max_poids;

子查询先计算每个颜色的最大重量，再通过颜色和重量的双重匹配，关联得到对应的des_字段。

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