mysql存储过程行列转换

BEGIN
SET @sql = '';
set @where= concat(" server_time>'",start_date,"' and idaction_event_category is not null ");
SELECT GROUP_CONCAT(DISTINCT CONCAT( 'IF(left(server_time,10) = ''', left(server_time,10), ''', count(*), 0) AS ''', left(server_time,10), '''' ) ) INTO @sql FROM `tj_log_link_visit_action` where @where;
SET @sql = CONCAT('SELECT (select name from tj_log_action where idaction = a.idaction_event_category),(select name from tj_log_action where idaction = a.idaction_event_action),(select name from tj_log_action where idaction = a.idaction_name) ,count(*),left(server_time,10),',"IF(left(server_time,10) = '2019-03-28', count(*), 0) AS '2019-03-28' ",' FROM `tj_log_link_visit_action` a where ',@where,' group by idaction_event_category');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `tj`(IN `type` int,IN `hasbrand` tinyint,IN `start_date` VARCHAR(20),IN `end_data` VARCHAR(20),IN `perid` tinyint)
BEGIN
SET @sql = '';
if perid = 0 then
set @timelen = 10;
else
set @timelen = 7;
END if;
set @where= CONCAT("server_time>='",start_date," 00:00:01' and server_time<='",end_data," 23:59:59' and idaction_event_category is not null  and idaction_event_category=",type);
select GROUP_CONCAT(CONCAT( 'IF(rq = ''', rq, ''', num, 0) AS ''',rq, '''' )) INTO @sql from (select DISTINCT left(server_time,@timelen) as rq from matomo_log_link_visit_action where server_time>=CONCAT(start_date,' 00:00:01') and server_time<=CONCAT(end_data,' 23:59:59') and idaction_event_category is not null  and idaction_event_category=type) a;
if hasbrand = 0 then
SET @sql = CONCAT('SELECT (SELECT NAME FROM matomo_log_action WHERE idaction = b.idaction_event_category) AS "类型",
    ( SELECT NAME FROM matomo_log_action WHERE idaction = b.idaction_event_action ) AS "事件分类",',@sql,'FROM     (SELECT    idaction_event_category,idaction_event_action,count(*) AS "num",LEFT ( server_time, ',@timelen,' ) as "rq" FROM `matomo_log_link_visit_action` a where ',@where,' GROUP BY idaction_event_category,idaction_event_action,LEFT(server_time,',@timelen,') ORDER BY LEFT ( server_time, ',@timelen,' ) asc) b');
else
SET @sql = CONCAT('SELECT (SELECT NAME FROM matomo_log_action WHERE idaction = b.idaction_event_category) AS "类型",
    ( SELECT NAME FROM matomo_log_action WHERE idaction = b.idaction_event_action ) AS "事件分类",
    ( SELECT NAME FROM matomo_log_action WHERE idaction = b.idaction_name ) AS "品牌",',@sql,'FROM     (SELECT    idaction_event_category,idaction_event_action,idaction_name,count(*) AS "num",LEFT ( server_time, ',@timelen,' ) as "rq" FROM `matomo_log_link_visit_action` a where ',@where,' GROUP BY idaction_event_category,idaction_event_action,idaction_name,LEFT(server_time,',@timelen,') ORDER BY LEFT ( server_time, ',@timelen,' ) asc) b');
END if;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END