如何修改SQL查询对名称相似的分组进行合并求和？

需要对SQL查询结果中名称相似的字段对应数值进行求和合并：现有SQL按name字段分组返回不含税总销售额totale_fatturato，需将类似"Climatizzazione"与"Climatizzatori Samsung"、"Climatizzatori Daiki"这类名称相似的分组合并为同一分组并计算总和，最终得到如下格式的聚合结果：

现有SQL语句如下：

SELECT 
cl.name, 
 SUM(od.total_price_tax_excl) AS totale_fatturato
FROM `ww_ps_order_detail` AS `od`
INNER JOIN `ww_ps_product` AS p ON od.product_id = p.id_product
INNER JOIN `ww_ps_category_lang` AS cl ON p.id_category_default = cl.id_category
WHERE od.ID_ORDER IN (
SELECT ID_ORDER
FROM ww_ps_orders
WHERE 1=1 AND DATE_ADD >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AND cl.id_lang = 1
GROUP BY name
ORDER BY totale_fatturato DESC
LIMIT 10

场景1：固定匹配规则（明确需合并的名称） #

如果能明确列出要合并的名称规则，用CASE WHEN语句统一分组名称，修改后的SQL如下：

SELECT 
  CASE
    -- 将包含"Climatizzatori"的名称统一归为"Climatizzazione"
    WHEN cl.name LIKE '%Climatizzatori%' THEN 'Climatizzazione'
    -- 可继续添加其他合并规则，例如：
    -- WHEN cl.name LIKE '%Scaldabagn%' THEN 'Scaldabagni'
    ELSE cl.name
  END AS group_name,
  SUM(od.total_price_tax_excl) AS totale_fatturato
FROM `ww_ps_order_detail` AS `od`
INNER JOIN `ww_ps_product` AS p ON od.product_id = p.id_product
INNER JOIN `ww_ps_category_lang` AS cl ON p.id_category_default = cl.id_category
WHERE od.ID_ORDER IN (
  SELECT ID_ORDER
  FROM ww_ps_orders
  WHERE DATE_ADD >= DATE_SUB(NOW(), INTERVAL 30 DAY)
) AND cl.id_lang = 1
-- 按CASE生成的统一分组名称聚合
GROUP BY group_name
ORDER BY totale_fatturato DESC
LIMIT 10

场景2：模糊匹配（基于关键词自动合并） #

如果需要更灵活的模糊匹配（比如提取名称核心前缀/关键词），可以用字符串正则或截取函数（以下以MySQL为例）：

SELECT 
  CASE
    -- 匹配以"Climatizz"开头的名称，统一归为"Climatizzazione"
    WHEN cl.name REGEXP '^Climatizz' THEN 'Climatizzazione'
    -- 匹配包含"Scaldabagn"的名称，统一归为"Scaldabagni"
    WHEN cl.name REGEXP 'Scaldabagn' THEN 'Scaldabagni'
    ELSE cl.name
  END AS group_name,
  SUM(od.total_price_tax_excl) AS totale_fatturato
FROM `ww_ps_order_detail` AS `od`
INNER JOIN `ww_ps_product` AS p ON od.product_id = p.id_product
INNER JOIN `ww_ps_category_lang` AS cl ON p.id_category_default = cl.id_category
WHERE od.ID_ORDER IN (
  SELECT ID_ORDER
  FROM ww_ps_orders
  WHERE DATE_ADD >= DATE_SUB(NOW(), INTERVAL 30 DAY)
) AND cl.id_lang = 1
GROUP BY group_name
ORDER BY totale_fatturato DESC
LIMIT 10

进阶方案：映射表分组 #

如果合并规则较多，建议创建一个分组映射表（例如category_group_map），结构如下：

然后通过JOIN实现分组，SQL示例：

SELECT 
  COALESCE(cgm.group_name, cl.name) AS group_name,
  SUM(od.total_price_tax_excl) AS totale_fatturato
FROM `ww_ps_order_detail` AS `od`
INNER JOIN `ww_ps_product` AS p ON od.product_id = p.id_product
INNER JOIN `ww_ps_category_lang` AS cl ON p.id_category_default = cl.id_category
LEFT JOIN category_group_map cgm ON cl.name LIKE cgm.original_name_pattern
WHERE od.ID_ORDER IN (
  SELECT ID_ORDER
  FROM ww_ps_orders
  WHERE DATE_ADD >= DATE_SUB(NOW(), INTERVAL 30 DAY)
) AND cl.id_lang = 1
GROUP BY group_name
ORDER BY totale_fatturato DESC
LIMIT 10

内容的提问来源于stack exchange，提问作者s.lafrag