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

sulc1iza 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(238)

我试图对预写查询中的行值求和，并将它们作为总计打印到单独的行中，最后使用汇总，但没有得到所需的输出。有谁能帮我在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;

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

期望输出：

sql mysql

来源：https://stackoverflow.com/questions/53959103/mysql-how-to-sum-all-row-values-from-the-returned-output-and-print-the-total-at

1条答案

按热度按时间

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;

分组参考

赞(0）回复(0）举报 2021-06-18

我来回答

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

1条答案

相关问题

热门标签

最新问答