SELECT
DATE_FORMAT(_scan_time, '%Y%m%d') as '日期',
count(_id) as '一天的總量',
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 '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點',
count(HOUR(_scan_time)='14' or null ) as '14點',
count(HOUR(_scan_time)='15' or null ) as '15點',
count(HOUR(_scan_time)='16' or null ) as '16點',
count(HOUR(_scan_time)='17' or null ) as '17點',
count(HOUR(_scan_time)='18' or null ) as '18點',
count(HOUR(_scan_time)='19' or null ) as '19點',
count(HOUR(_scan_time)='20' or null ) as '20點',
count(HOUR(_scan_time)='21' or null ) as '21點',
count(HOUR(_scan_time)='22' or null ) as '22點',
count(HOUR(_scan_time)='23' or null ) as '23點'
FROM
user_
GROUP BY
DATE_FORMAT(_scan_time, '%Y%m%d')
uj5u.com熱心網友回復：
uj5u.com熱心網友回復：
你可以試試這個動態的陳述句SET @@GROUP_CONCAT_MAX_LEN = 1024000;
set @sql='' ;
select @sql:=
group_concat(
concat('count(HOUR(_scan_time))=''',number,''' or null as ''',number,' 點''')
,'')from sp_values
where number between 1 and 24;
set @sql =concat('select DATE_FORMAT(_scan_time, ''%Y%m%d''),',@sql,' from _user group by DATE_FORMAT(_scan_time, ''%Y%m%d'')');
select @sql
表sp_values 里的資料只是編號而已
uj5u.com熱心網友回復：
SET @@GROUP_CONCAT_MAX_LEN = 1024000;set @sql=NULL ;
select @sql:=
group_concat(
concat('count(HOUR(_scan_time)=''',number,''' or null )as ''',number,' 點''')
,'')
into @sql
from sp_values
where number between 1 and 24;
set @sql:=concat('select DATE_FORMAT(_scan_time, ''%Y%m%d''),',@sql,' from _user group by DATE_FORMAT(_scan_time, ''%Y%m%d'')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
uj5u.com熱心網友回復：
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/73682.html
標籤：MySQL
上一篇：Oracle遷移到MySQL，其中有段SQL陳述句需要轉換成MySQL的語法，絞盡腦汁幾天了，寫不出來，求大神指教！