Я хочу вместо Null 0 и добавить subtotal в конце столбца и строки. как я могу измениться?
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 вместо нуля. WITH ROLLUP вместе с coalesce(playerid, ''Column Total'') заботится о итоговых значениях столбца, а sum(score) as row_total учитывает итоговые значения строк.