MySQL - 在 SUM 计算后合并两个数字

Question

这是我的原始查询，它收集了我试图合并并变成饼图的数据:

select 
`Type of Truck`,
SUM(Profit)
From `table1`
where YEAR(date_of_sale) = 2019 and month(date_of_sale) = 10
GROUP BY YEAR(date_of_sale), month(date_of_sale),`Type of Truck`
ORDER BY `Type of Truck` desc;

我的结果是:

Truck Profit
9   47919
8   225
7   -1170
6   293767
5   50634
4   14304
3   30654
21  13484
20  1610
2   288226
19  29134
18  2200
17  575
16  21911
15  1760
14  73476
13  345
12  48680
11  63922
10  29941
1   398265

我需要合并 Truck 1 和 2，然后将结果 SUM 显示到我的饼图中。现在他们是分开的。还需要合并 Truck 4 和 5。

这是我当前的查询:

select *
From


(
select 
`Type of Truck`,
SUM(Profit)
From `table1`
where YEAR(date_of_sale) = 2019 and month(date_of_sale) = 10 AND `Type of Truck` NOT IN(1,2,3,4)
GROUP BY YEAR(date_of_sale), month(date_of_sale),`Type of Truck`
) as a 
,
(
select 
SUM(Profit) as 'FB48'
FROM `table1`
WHERE YEAR(date_of_sale) = 2019 and month(date_of_sale) = 10 AND `Type of Truck` IN(1,2) 

) AS b


;
ORDER BY `Type of Truck` desc

问题是这个查询生成了一个新列，我需要它的组合 SUM 与 Profit 在同一列中，这样我的饼图才能正确显示数字。

感谢任何帮助!

Answer 1

由于您想要组合数字，您可以将卡车类型组合为单个分组标准，如下所示:

SELECT 
   CASE WHEN `Type of Truck` IN (1, 2) THEN '1,2' 
       WHEN `Type of Truck` IN (4, 5) THEN '4,5' 
       ELSE `Type of Truck` 
     END AS ReportingType
   , SUM(Profit)
FROM `table1`
WHERE YEAR(date_of_sale) = 2019 AND MONTH(date_of_sale) = 10
GROUP BY YEAR(date_of_sale), MONTH(date_of_sale),`ReportingType`
ORDER BY `ReportingType` DESC
;

另请注意，由于您的 where 条件将您考虑的数据限制在特定年份和月份，因此您不需要按年份和月份分组。

---

如果您想保持原始查询的简单性，或者发现上述方法会减慢速度(它可能会干扰索引的使用)，您也可以采用这种方式。

SELECT CASE WHEN `Type of Truck` IN (1, 2) THEN '1,2' 
       WHEN `Type of Truck` IN (4, 5) THEN '4,5' 
       ELSE `Type of Truck` 
     END AS ReportingType
   , SUM(Profit) AS Profit
FROM (
   SELECT `Type of Truck`, SUM(Profit) AS Profit
   FROM `table1`
   WHERE YEAR(date_of_sale) = 2019 AND MONTH(date_of_sale) = 10
   GROUP BY YEAR(date_of_sale), MONTH(date_of_sale),`Type of Truck`
) AS subQ
GROUP BY `ReportingType`
ORDER BY `ReportingType` DESC
;

这样，子查询可以更直接地利用索引，组合外部查询只需对原始结果中的几行求和即可。