SQL每日一题(20220429)
SELECT(
SELECT GROUP_CONCAT(CONCAT('SUM(IF(d =', d, ',wt,0)) as ', concat(d, '日')))
from (select distinct day(dt) as d from t1125) t)
as aa
into @str_tmp;
select(
select concat('select ifnull(new_date,\'total\') as new_date,SUM(wt) AS TOTAL,', @str_tmp,
' FROM (select DATE_FORMAT(dt,\'%Y-%m\') as new_date,WT,day(dt) as d from t1125) f GROUP BY new_date WITH ROLLUP'))
as bb
into @s;
PREPARE stmt FROM @s;
EXECUTE stmt;
deallocate prepare stmt;