mysql - SQL查询合并两个字段的总数

Question

我应该根据我的要求合并两个结果字段。例如，下面的查询提供如图所示的结果。但我需要一个 case 语句来声明“wind”和“wind c”相同，因此将其总数合并起来。

SELECT result, COUNT(*) AS total from(
SELECT t.Id AS Id,
  CASE 
   WHEN LTRIM(RTRIM(AC)) = ' ' THEN AG 
   WHEN LOWER(AC) IS NOT NULL THEN LOWER(AC) 
   ELSE LOWER(AG) 
  END AS result,MaxDate,
FROM [DC_20160601] t
INNER JOIN ( SELECT Id, MAX(DateTime) AS MaxDate
FROM [DC_20160601]
GROUP BY Id) tm
ON t.Id = tm.Id AND t.DateTime = tm.MaxDate)
GROUP BY result

当前结果:

Result    Total
Wind      1212
Wind c    345
sqrim     321
sqrim mob 123

预期结果:

Result      Total
Wind mobile  1557
Sqrim mobile 444

Answer 1

您可以通过substring_index选择字符串中的第一个单词

SELECT SUBSTRING_INDEX(`result`, ' ', 1);

现在按结果的第一个单词进行分组

SELECT SUBSTRING_INDEX(`result`, ' ', 1), COUNT(*) AS total from(
SELECT t.Id AS Id,
  CASE 
   WHEN LTRIM(RTRIM(AC)) = ' ' THEN AG 
   WHEN LOWER(AC) IS NOT NULL THEN LOWER(AC) 
   ELSE LOWER(AG) 
  END AS result,MaxDate,
FROM [DC_20160601] t
INNER JOIN ( SELECT Id, MAX(DateTime) AS MaxDate
FROM [DC_20160601]
GROUP BY Id) tm
ON t.Id = tm.Id AND t.DateTime = tm.MaxDate)
GROUP BY SUBSTRING_INDEX(`result`, ' ', 1)