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')
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')
解决方案 »
- linux下mysql如何查看back_log大小
- 多表查询的关联问题!
- mysql中字符串输入问题
- mysql 远程连接
- 请教怎样利用smartupload组件上传文件到mysql
- ★★★★★关于postgresql中建表的问题,高手请进啊☆☆☆☆☆
- 请问这个sql语句怎么写?
- 我在winXP下安装了mysql,为什么在VC中无法使用mysql的c api?
- 经过一段时间本人修改了sendmail、POP3、IMAP4的源代码,把用户认证该为了数据库,数据库采用的MYSQL、并虚拟了整个系统用户来存放邮件内容,有意者请与我联系[email protected]
- 查找并修改products_name不含android的其他产品的
- MySQL5.7目录中没有my.ini 还有ProgramData/MySQL文件夹也是空的
- mysql 叶子节点问题
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 里的数据只是编号而已
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;