SQLite笔记,查询语句:日、周、月

前端之家收集整理的这篇文章主要介绍了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 常用函数

原文链接:https://www.f2er.com/sqlite/198332.html

猜你在找的Sqlite相关文章