开发环境,Asp.net(C#) 加MySql。统计报表问题。 现在我有一张用户表User,用户表里有用户注册时间,使用统计select count(*),RegisterTime,Substring(RegisterTime,1,7) from User group by Substring(RegisterTime,1,7)统计结果是:
但是我的报表统计结果希望是:请问用MySql怎么实现这样的报表。。没有注册用户的月份显示0.
但是我的报表统计结果希望是:请问用MySql怎么实现这样的报表。。没有注册用户的月份显示0.
select sum(if(Substring(RegisterTime,6,2)='01',1,0)) as 1,
sum(if(Substring(RegisterTime,6,2)='02',2,0)) as 2,
sum(if(Substring(RegisterTime,6,2)='03',1,0)) as 3,....
sum(if(Substring(RegisterTime,6,2)='12',1,0)) as 12
from tt
SUM(IF(SUBSTRING(RegisterTime,6,2)='02',2,0)) AS 2,
SUM(IF(SUBSTRING(RegisterTime,6,2)='03',1,0)) AS 3,
....
SUM(IF(SUBSTRING(RegisterTime,6,2)='12',1,0)) AS 12
FROM YOURTABLE.
[align=center]==== 思想重于技巧 ====
[/align]
.
贴子分数<20:对自已的问题不予重视。
贴子大量未结:对别人的回答不予尊重。
.
SQL 1992
MySQL 5.1 Reference Manual
ActiveX Data Objects 2.5 Reference 下载
数据库系统概论PPT.
[align=center]==== 思想重于技巧 ====
[/align]
.
贴子分数<20:对自已的问题不予重视。
贴子大量未结:对别人的回答不予尊重。
.
SUM(IF(SUBSTRING(RegisterTime,6,2)='01',1,0)) AS '1',
SUM(IF(SUBSTRING(RegisterTime,6,2)='02',2,0)) AS '2',
SUM(IF(SUBSTRING(RegisterTime,6,2)='03',3,0)) AS '3',
SUM(IF(SUBSTRING(RegisterTime,6,2)='03',4,0)) AS '4',
SUM(IF(SUBSTRING(RegisterTime,6,2)='03',5,0)) AS '5',
SUM(IF(SUBSTRING(RegisterTime,6,2)='03',6,0)) AS '6',
SUM(IF(SUBSTRING(RegisterTime,6,2)='03',7,0)) AS '7',
SUM(IF(SUBSTRING(RegisterTime,6,2)='03',8,0)) AS '8',
SUM(IF(SUBSTRING(RegisterTime,6,2)='03',9,0)) AS '9',
SUM(IF(SUBSTRING(RegisterTime,6,2)='03',10,0)) AS '10',
SUM(IF(SUBSTRING(RegisterTime,6,2)='03',11,0)) AS '11',
SUM(IF(SUBSTRING(RegisterTime,6,2)='12',12,0)) AS '12'
FROM mvb2k400_accountmain group by SUBSTRING(RegisterTime,1,4),SUBSTRING(RegisterTime,6,2);
怎么统计出来的全都是0???而且这样做的性能会不会很差?
SUM(IF(SUBSTRING(RegisterTime,6,2)='01',1,0)) AS '1',
SUM(IF(SUBSTRING(RegisterTime,6,2)='02',1,0)) AS '2',
SUM(IF(SUBSTRING(RegisterTime,6,2)='03',1,0)) AS '3',
SUM(IF(SUBSTRING(RegisterTime,6,2)='04',1,0)) AS '4',
SUM(IF(SUBSTRING(RegisterTime,6,2)='05',1,0)) AS '5',
SUM(IF(SUBSTRING(RegisterTime,6,2)='06',1,0)) AS '6',
SUM(IF(SUBSTRING(RegisterTime,6,2)='07',1,0)) AS '7',
SUM(IF(SUBSTRING(RegisterTime,6,2)='08',1,0)) AS '8',
SUM(IF(SUBSTRING(RegisterTime,6,2)='09',1,0)) AS '9',
SUM(IF(SUBSTRING(RegisterTime,6,2)='10',1,0)) AS '10',
SUM(IF(SUBSTRING(RegisterTime,6,2)='11',1,0)) AS '11',
SUM(IF(SUBSTRING(RegisterTime,6,2)='12',1,0)) AS '12'
FROM mvb2k400_accountmain group by SUBSTRING(RegisterTime,1,4),SUBSTRING(RegisterTime,6,2);但不知道性能怎么样??