mysql中静态行转列和动态行转列

需求：报表展示，统计邀请人每天的邀请人数。

SELECT
	cii.inviter_area,
	cii.inviter_marketing_department,
	cii.inviter_organization_three,
	cii.inviter_organization_four,
	DATE_FORMAT(ir.create_time, '%Y-%m-%d') AS create_time,
	ir.inviter_phone AS inviter_phone,
	ir.inviter_name AS inviter_name,
	COUNT(ir.inviteer) AS inviter_num
FROM
	zhongtou.t22000_invite_response AS ir
LEFT JOIN zhongtou.c_inviter_identify cii ON cii.id = ir.inviter_phone
WHERE
	ir.inviter_phone IS NOT NULL
GROUP BY
	inviter,
	DATE_FORMAT(create_time, '%Y-%m-%d')
ORDER BY
	inviter_num DESC

需求+1：要求将日期行转换为日期列。

静态行转列：

按照月份进行统计，(按月进行统计)邀请人每天的邀请人数。

SELECT
	cii.inviter_area,
	cii.inviter_marketing_department,
	cii.inviter_organization_three,
	cii.inviter_organization_four,
	ir.inviter_phone AS inviter_phone,
	ir.inviter_name AS inviter_name,
	SUM(CASE WHEN DAYOFMONTH(ir.create_time)='1' THEN 1 ELSE 0 END ) '1号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='2' THEN 1 ELSE 0 END ) '2号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='3' THEN 1 ELSE 0 END ) '3号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='4' THEN 1 ELSE 0 END ) '4号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='5' THEN 1 ELSE 0 END ) '5号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='6' THEN 1 ELSE 0 END ) '6号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='7' THEN 1 ELSE 0 END ) '7号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='8' THEN 1 ELSE 0 END ) '8号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='9' THEN 1 ELSE 0 END ) '9号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='10' THEN 1 ELSE 0 END ) '10号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='11' THEN 1 ELSE 0 END ) '11号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='12' THEN 1 ELSE 0 END ) '12号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='13' THEN 1 ELSE 0 END ) '13号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='14' THEN 1 ELSE 0 END ) '14号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='15' THEN 1 ELSE 0 END ) '15号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='16' THEN 1 ELSE 0 END ) '16号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='17' THEN 1 ELSE 0 END ) '17号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='18' THEN 1 ELSE 0 END ) '18号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='19' THEN 1 ELSE 0 END ) '19号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='20' THEN 1 ELSE 0 END ) '20号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='21' THEN 1 ELSE 0 END ) '21号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='22' THEN 1 ELSE 0 END ) '22号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='23' THEN 1 ELSE 0 END ) '23号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='24' THEN 1 ELSE 0 END ) '24号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='25' THEN 1 ELSE 0 END ) '25号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='26' THEN 1 ELSE 0 END ) '26号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='27' THEN 1 ELSE 0 END ) '27号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='28' THEN 1 ELSE 0 END ) '28号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='29' THEN 1 ELSE 0 END ) '29号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='30' THEN 1 ELSE 0 END ) '30号',
	SUM(CASE  WHEN DAYOFMONTH(ir.create_time)='31' THEN 1 ELSE 0 END ) '31号'
FROM
	zhongtou.t22000_invite_response AS ir
LEFT JOIN zhongtou.c_inviter_identify cii ON cii.id = ir.inviter_phone
WHERE
	ir.inviter_phone IS NOT NULL
GROUP BY
	ir.inviter

动态行转列

SET @str = '';
SET @sql_temp = '';
SELECT
	@str := CONCAT(
		@str,
		'sum(IF(left(ir.create_time,10)=\'',
		create_time,
		'\'',
		',1,0)',
		') AS ',
		'''',
		create_time,
		'''',
		','
	) AS aa INTO @sql_temp
FROM
	(
		SELECT
			LEFT (ir.create_time, 10) AS create_time
		FROM
			zhongtou.t22000_invite_response ir
		GROUP BY
			LEFT (ir.create_time, 10)
	) A
ORDER BY
	length(aa) DESC
LIMIT 1;


SET @result_sql = CONCAT(
	'SELECT  cii.inviter_area,cii.inviter_marketing_department,cii.inviter_organization_three,cii.inviter_organization_four,ir.inviter_phone AS inviter_phone,
	ir.inviter_name AS inviter_name, ',
	LEFT (
		@sql_temp,
		char_length(@sql_temp) - 1
	),
	'  FROM zhongtou.t22000_invite_response ir LEFT JOIN zhongtou.c_inviter_identify cii ON cii.id = ir.inviter_phone
WHERE
	ir.inviter_phone IS NOT NULL  
GROUP BY inviter'
);

PREPARE stmt
FROM
	@result_sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt