Write their own mysql according to hours grouped to statistical data of a day, I hope it can help ot-CodePudding

SELECT
DATE_FORMAT (_scan_time, '% Y % m % d') as a 'date',
Count (_id) as the amount of 'day',
Count (HOUR (_scan_time)='0' or null) as' 0 ',
Count (HOUR (_scan_time)='1' or null) as' 1 ',
Count (HOUR (_scan_time)='2' or null) as' 2 ',
Count (HOUR (_scan_time)='3' or null) as' 3 ',
Count (HOUR (_scan_time)='4' or null) as a '4',
Count (HOUR (_scan_time)='5' or null) as' 5 ',
Count (HOUR (_scan_time)='6' or null) as' 6 ',
Count (HOUR (_scan_time)='7' or null) as' 7 ',
Count (HOUR (_scan_time)='8' or null) as' 8 ',
Count (HOUR (_scan_time)='9' or null) as' 9 ',
Count (HOUR (_scan_time)='10' or null) as' 10 ',
Count (HOUR (_scan_time)='11' or null) as' 11 ',
Count (HOUR (_scan_time)='12' or null) as' 12 ',
Count (HOUR (_scan_time)='13' or null) as' 13 points,
Count (HOUR (_scan_time)='14' or null) as' 14 points,
Count (HOUR (_scan_time)='15' or null) as' 15 ',
Count (HOUR (_scan_time)='16' or null) as' 16 points,
Count (HOUR (_scan_time)='17' or null) as' 17 ',
Count (HOUR (_scan_time)='18' or null) as' 18 points,
Count (HOUR (_scan_time)='19' or null) as' 19 points,
Count (HOUR (_scan_time)='20' or null) as' 20 ',
Count (HOUR (_scan_time)='21' or null) as' 21 points,
Count (HOUR (_scan_time)='22' or null) as' 22 ',
Count (HOUR (_scan_time)='23' or null) as' 23 points'
FROM
User_

GROUP BY
DATE_FORMAT (_scan_time, '% Y % m % d')

CodePudding user response:

You can try this dynamic statement
The SET @ @ GROUP_CONCAT_MAX_LEN=1024000;
The set @ SQL=' ';
Select @ SQL:=
Group_concat (
Concat (' count (HOUR (_scan_time))=' ' ', number, ' ' 'or null as'' ', the number of 'points'' ')
', ') from sp_values
Where the number between 1 and 24.
Set @ SQL=concat (' select DATE_FORMAT (_scan_time '% Y % m % d'), ', @ SQL, 'the from _user group by DATE_FORMAT (_scan_time' % Y % m % d ') ');
Select @ SQL

The data in the table sp_values just number

CodePudding user response:

The SET @ @ GROUP_CONCAT_MAX_LEN=1024000;
The set @ SQL=NULL;
Select @ SQL:=
Group_concat (
Concat (' count (HOUR (_scan_time)=' ' ', number, ' ' 'or null) as'' ', the number of 'points'' ')
', ')
Into @ SQL
The from sp_values
Where the number between 1 and 24.
Set @ SQL:=concat (' select DATE_FORMAT (_scan_time '% Y % m % d'), ', @ SQL, 'the from _user group by DATE_FORMAT (_scan_time' % Y % m % d ') ');
PREPARE STMT FROM @ SQL;
The EXECUTE STMT.
DEALLOCATE PREPARE STMT.

CodePudding user response: