一、查询当天
SELECT sum(NumericCount),TrainingDate FROM TableTrainingRecord WHERE strftime('%Y-%m-%d','2017-01-01','localtime') <= strftime('%Y-%m-%d',TrainingDate)
二、按天统计,合并一天的多条数据:(%Y 年,数字,4 位 ;%y 年,2 位)
1.
select strftime('%Y-%m-%d',TrainingDate),strftime('%m',strftime('%d',sum(NumericCount)
from TableTrainingRecord group by strftime('%Y-%m-%d',TrainingDate)
2.
select strftime('%Y年%m-%d',TrainingDate) as Y_M_D,TrainingDate) as month,TrainingDate) as day,sum(NumericCount) as number
from TableTrainingRecord group by strftime('%Y-%m-%d',TrainingDate)
3.简化
select strftime('%Y年%m-%d',TrainingDate) as TrainingDate,sum(NumericCount) as NumericCount
from TableTrainingRecord group by strftime('%Y-%m-%d',TrainingDate)
三、按周统计(%W:一年中的第几周,比如,01-53,第53周)
select strftime('%Y-%W',sum(NumericCount) as NumericCount,sum(ReadTime) as ReadTime,sum(WriteTime) as WriteTime,sum(ErrorNumericCount) as ErrorNumericCount
from TableTrainingRecord group by strftime('%Y-%W',TrainingDate)
四、按月,统计一整个月:上面按天语句,去掉:%d
select strftime('%Y年%m',sum(NumericCount) as number,sum(ReadTime)
from TableTrainingRecord group by strftime('%Y-%m',TrainingDate)
五、统计最大,总数、总量、平均
SELECT sum(ReadTime+WriteTime)/60 as sumTime,count(systemId) as sumCount,sum(NumericCount) as sumNumeric,max(ReadTime+WriteTime)/60 as maxTime,max(NumericCount) as maxNumeric,(1-1.0*sum(ErrorNumericCount)/sum(NumericCount))*100 as avgCorrectPercent,sum(NumericCount)/(sum(ReadTime)/60) as avgRead,sum(NumericCount)/(sum(WriteTime)/60) as avgWrite FROM TableTrainingRecord