将mysql数据透视表代码更改为php [英] change mysql pivot table code into php

问题描述

我想将 Null 改为 0 并在列和行的末尾添加小计.怎么改?

I want to instead Null to 0 and add subtotal at the end of column and row. how can I change?

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
  'sum(case when gameid = ''',
  gameid,
  ''' then score end) AS ''',
  gameid, ''''
)
  ) INTO @sql
FROM  scores;

SET @sql = CONCAT('SELECT playerid, ', @sql, '
              FROM scores
               GROUP BY playerid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

推荐答案

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
  'sum(case when gameid = ''',
  gameid,
  ''' then score else 0 end) AS ''',
  gameid, ''''
)
  ) INTO @sql
FROM  scores;

SET @sql = CONCAT('SELECT coalesce(playerid, ''Column Total'') as playerid, ', @sql, ', sum(score) as row_total
              FROM scores
               GROUP BY playerid WITH ROLLUP');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

else 0 负责传递 0 而不是 null.WITH ROLLUP 与 coalesce(playerid, ''Column Total'') 一起处理列总数，而 sum(score) as row_total 负责行总计.

The else 0 takes care to deliver 0 instead of null. The WITH ROLLUP together with the coalesce(playerid, ''Column Total'') takes care of the column totals, and the sum(score) as row_total takes care of the row totals.

这篇关于将mysql数据透视表代码更改为php的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！