mysql 按日期，以及时间段统计数据，需要列转行，求大神！！

[htmltitle]

table A
userId sendId regTime
1 1 2018-07-16 00:10:26
2 1 2018-07-16 01:10:26
3 1 2018-07-16 02:10:26
6 2 2018-07-16 21:10:26
7 2 2018-07-16 22:10:26
8 2 2018-07-16 23:10:26
9 2 2018-07-15 20:10:26
10 2 2018-07-15 20:10:26
11 2 2018-07-15 20:10:26
table B
sendId sendName
1 数据1
2 数据2


结果

日期 sendName H00 H01 H02 ----------------------- H21 H22 H23

2018-07-16 数据1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2018-07-16 数据2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1
2018-07-15 数据2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3 0 0 0

[楓VS痕]

select c.regTimes,c.sendName,SUM(c.H00) H00,SUM(c.H01) H01,SUM(c.H02) H02,
	SUM(c.H03) H03,SUM(c.H04) H04,SUM(c.H05) H05,SUM(c.H06) H06,SUM(c.H07) H07,
	SUM(c.H08) H08,SUM(c.H09) H09,SUM(c.H10) H10,SUM(c.H11) H11,SUM(c.H12) H12,
	SUM(c.H13) H13,SUM(c.H14) H14,SUM(c.H15) H15,SUM(c.H16) H16,SUM(c.H17) H17,
	SUM(c.H18) H18,SUM(c.H19) H19,SUM(c.H20) H20,SUM(c.H21) H21,SUM(c.H22) H22,SUM(c.H23) H23
FROM 
	(SELECT SUBSTR(a.regTime,1,10) regTimes, a.sendId, b.sendName,
		IF(SUBSTR(a.regTime,12,2)='00', 1, 0) H00, IF(SUBSTR(a.regTime,12,2)='01', 1, 0) H01,
		IF(SUBSTR(a.regTime,12,2)='02', 1, 0) H02, IF(SUBSTR(a.regTime,12,2)='03', 1, 0) H03,
		IF(SUBSTR(a.regTime,12,2)='04', 1, 0) H04, IF(SUBSTR(a.regTime,12,2)='05', 1, 0) H05,
		IF(SUBSTR(a.regTime,12,2)='06', 1, 0) H06, IF(SUBSTR(a.regTime,12,2)='07', 1, 0) H07,
		IF(SUBSTR(a.regTime,12,2)='08', 1, 0) H08, IF(SUBSTR(a.regTime,12,2)='09', 1, 0) H09,
		IF(SUBSTR(a.regTime,12,2)='10', 1, 0) H10, IF(SUBSTR(a.regTime,12,2)='11', 1, 0) H11,
		IF(SUBSTR(a.regTime,12,2)='12', 1, 0) H12, IF(SUBSTR(a.regTime,12,2)='13', 1, 0) H13,
		IF(SUBSTR(a.regTime,12,2)='14', 1, 0) H14, IF(SUBSTR(a.regTime,12,2)='15', 1, 0) H15,
		IF(SUBSTR(a.regTime,12,2)='16', 1, 0) H16, IF(SUBSTR(a.regTime,12,2)='17', 1, 0) H17,
		IF(SUBSTR(a.regTime,12,2)='18', 1, 0) H18, IF(SUBSTR(a.regTime,12,2)='19', 1, 0) H19,
		IF(SUBSTR(a.regTime,12,2)='20', 1, 0) H20, IF(SUBSTR(a.regTime,12,2)='21', 1, 0) H21,
		IF(SUBSTR(a.regTime,12,2)='22', 1, 0) H22, IF(SUBSTR(a.regTime,12,2)='23', 1, 0) H23
	FROM table_a a
	LEFT JOIN table_b b ON b.sendId = a.sendId) c
GROUP BY c.regTimes, c.sendId
ORDER BY regTimes DESC