SQLite笔记，查询语句：日、周、月

一、查询当天

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

MySql按周，按月，按日分组统计数据

SQLite 常用函数

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!