mysql:如何对返回的输出中的所有行值求和,并在最后一次输出时打印总数

sulc1iza  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(237)

我试图对预写查询中的行值求和,并将它们作为总计打印到单独的行中,最后使用汇总,但没有得到所需的输出。有谁能帮我在mysql中实现这个条件吗。?
查询:

SELECT client, 
       flag, 
       start_hour, 
       Group_concat(IF(deploy_time='8', count, NULL))  AS '8h', 
       Group_concat(IF(deploy_time='9', count, NULL))  AS '9h', 
       Group_concat(IF(deploy_time='10', count, NULL)) AS '10h', 
       Group_concat(IF(deploy_time='11', count, NULL)) AS '11h', 
       Group_concat(IF(deploy_time='16', count, NULL)) AS '16h', 
       Group_concat(IF(deploy_time='17', count, NULL)) AS '17h' 
FROM   (SELECT client, 
               Sum(count)                                   count, 
               start_hour, 
               flag, 
               IF(deploy_time = 0, start_hour, deploy_time) deploy_time 
        FROM   sample 
        GROUP  BY 1, 
                  3, 
                  4, 
                  5) tb1 
GROUP  BY client, 
          flag, 
          start_hour 
ORDER  BY 1, 
          3;

查询输出:附上截图供参考

期望输出:

js81xvg6

js81xvg61#

尝试分组汇总

SELECT client, 
       flag, 
       start_hour, 
       Group_concat(IF(deploy_time='8', count, NULL))  AS '8h', 
       Group_concat(IF(deploy_time='9', count, NULL))  AS '9h', 
       Group_concat(IF(deploy_time='10', count, NULL)) AS '10h', 
       Group_concat(IF(deploy_time='11', count, NULL)) AS '11h', 
       Group_concat(IF(deploy_time='16', count, NULL)) AS '16h', 
       Group_concat(IF(deploy_time='17', count, NULL)) AS '17h' 
FROM   (SELECT client, 
               Sum(count)                                   count, 
               start_hour, 
               flag, 
               IF(deploy_time = 0, start_hour, deploy_time) deploy_time 
        FROM   sample 
        GROUP  BY 1, 
                  3, 
                  4, 
                  5) tb1 
GROUP  BY client, 
          flag, 
          start_hour 
WITH ROLLUP
HAVING grouping(client) = 1 and grouping(flag) = 1 and grouping(Starg_hour) =1
ORDER  BY 1, 
          3;

分组参考

相关问题