mysql查询明细和合计，如何在同一行不同列展示？

Question

CREATE TABLE `t_energy_statistic` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `invoiceEntryTime` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '时间',
  `degree` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '度数',
  `amount` decimal(12,2) DEFAULT NULL COMMENT '金额',
  `tax` decimal(12,2) DEFAULT NULL COMMENT '税额',
  `totalAmount` decimal(12,2) DEFAULT NULL COMMENT '总金额',
  `setUpCompanyId` int DEFAULT NULL COMMENT '开出公司',
  `receCompanyId` int DEFAULT NULL COMMENT '收到公司'
   `PRIMARY KEY (`id`) USING BTREE`
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin COMMENT='能耗统计表';

现在有一张能源统计表，总度数显示收到公司的各个月份的度数合计，A、B、C公司分别代表开出公司各个月份的度数合计，现在想实现EXCE表格那种效果，如上截图。用sql查询怎么实现？我尝试使用GROUP_CONCAT函数分割和SUBSTRING_INDEX多字符串行转列实现，但是遇到一个问题，degree个别时候长度不一致，我没办法区分A公司1月份合计开出多少度，最终的需求是想实现，收到公司度数的合计，和收到公司分别是由哪几个公司开出的度各月汇总度数。

--如下是我写是sql，由于3月份有多条数据，拆分的时候只拆分了两条，其实应该对应两家各有一条合计数degree1和degree2，然后degree1+degree2=degree
SELECT invoiceEntryTime invoiceentrytime,meterDate meterdate,sum(degree) degree,
    (select SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(degree),',',1),',',-1)) as degree1,
    (select SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(degree),',',2),',',-1)) as degree2 
    FROM t_energy_statistic AS A  where 1=1 and receCompanyId in ( '2' , '30' ) and invoiceEntryTime in ( '2023-01' , '2023-02' , '2023-03' ) GROUP BY A.invoiceEntryTime ORDER BY A.invoiceEntryTime;

Answer 1

哈喽，从您的描述和需求来看,这是一道典型的“行转列”的SQL查询问题。实现方法如下:

1. 使用GROUP_CONCAT()函数将各公司同月份的度数合并为一行,格式如:A1,B2,C3
2. 使用SUBSTR(),LENGTH()和INSTR()函数解析每个公司同月份的度数。例如:
   sql
   SUBSTR(度数串, 1, INSTR(度数串, ',') - 1) AS A公司度数
   SUBSTR(度数串, INSTR(度数串, ',') + 1, LENGTH(度数串) - INSTR(度数串, ',') - 1) AS B公司度数
3. 最后使用SUM()函数分组计算各月份总度数和各公司度数合计。
   具体SQL如下:
   sql
   SELECT
   DATE_FORMAT(date, '%Y%m') AS 月份,
   SUM(degree) AS 收到公司合计,
   SUM(SUBSTR(degrees, 1, INSTR(degrees, ',') - 1)) AS A公司,
   SUM(SUBSTR(degrees, INSTR(degrees, ',') + 1, LENGTH(degrees) - INSTR(degrees, ',') - 1 )) AS B公司,
   SUM(SUBSTR(degrees, INSTR(degrees, ',', INSTR(degrees, ',') + 1) + 1)) AS C公司
   FROM energy_table
   GROUP BY DATE_FORMAT(date, '%Y%m')
   该查询会按月份分组,求出每月总度数,以及各公司的度数贡献。 degrees列使用GROUP_CONCAT()函数连接各公司同月度数,然后使用子字符串函数解析每个公司的度数。
   这个方案实现了“行转列”的效果,能很好满足您的需求。如在实现或理解中有任何问题,欢迎提出,我将继续解答。希望这个SQL查询能为您提供帮助，祝君好运。