如何在MySQL中按产品混合属性列集获取对应值？

我明白你的需求了：当前查询会把产品的每个属性单独拆成一行返回，但你需要将同一产品、同一数量/价格组合的多个属性合并到同一行的不同列中（比如attvalue、attvalue2）。下面是具体的实现方案：

核心思路 #

我们需要先给每个产品-规格组（按prodid、qty、price分组）内的属性分配一个唯一序号，再通过条件聚合将不同序号的属性值映射到对应的列中，最终把分散的行合并成一行。

完整SQL查询 #

SELECT
    a.prodid,
    a.product,
    b.catid,
    b.category,
    COALESCE(MAX(CASE WHEN rn = 1 THEN d.attvalue END), '') AS attvalue,
    COALESCE(MAX(CASE WHEN rn = 2 THEN d.attvalue END), '') AS attvalue2,
    COALESCE(MAX(CASE WHEN rn = 3 THEN d.attvalue END), '') AS attvalue3,
    c.qty,
    c.price
FROM tbl_products a
LEFT JOIN tbl_categories b ON a.catid = b.catid
LEFT JOIN tbl_prodattval c ON a.prodid = c.prodid
LEFT JOIN (
    -- 给每个产品-规格组内的属性分配序号
    SELECT 
        c2.attvalid,
        d2.attvalue,
        ROW_NUMBER() OVER (
            PARTITION BY c2.prodid, c2.qty, c2.price 
            ORDER BY d2.attvalue
        ) AS rn
    FROM tbl_prodattval c2
    LEFT JOIN tbl_attvalues d2 ON c2.attvalid = d2.attvalid
) d ON c.attvalid = d.attvalid
GROUP BY a.prodid, a.product, b.catid, b.category, c.qty, c.price
ORDER BY a.prodid;

代码细节解释 #

1. 子查询生成属性序号：

   • 用ROW_NUMBER()窗口函数，按prodid、qty、price分组（确保同一规格的属性归为一组），给每个属性分配一个序号rn。
   • ORDER BY d2.attvalue是为了保证属性排序的一致性，你可以根据需求改成attvalid或其他字段。

2. 条件聚合合并属性列：

   • 使用MAX(CASE WHEN rn = N THEN d.attvalue END)，将序号为1的属性放到attvalue，序号2的放到attvalue2，以此类推。
   • 搭配COALESCE把空值替换成空字符串，让结果更符合你期望的格式。

3. 分组合并行：

   • 最后按prodid、product、catid、category、qty、price分组，确保同一规格的所有属性最终合并成一行。

扩展说明 #

如果你的产品属性可能超过3个，只需要继续添加COALESCE(MAX(CASE WHEN rn = 4 THEN d.attvalue END), '') AS attvalue4这类语句即可。

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