CREATE TABLE "aws_login" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"account" TEXT(50),
"password" REAL(50),
"remember" TEXT(50),
"lastTime" TEXT
);
-- ----------------------------
-- Records of aws_login
-- ----------------------------
INSERT INTO "main"."aws_login" VALUES (2,222554,123456.0,'2013-02-03 10:20:30');
INSERT INTO "main"."aws_login" VALUES (3,22,22.0,1,'2013-03-30');
INSERT INTO "main"."aws_login" VALUES (4,3213,2,'2013-03-29');
INSERT INTO "main"."aws_login" VALUES (5,234,234.0,324,'2013-03-28');
INSERT INTO "main"."aws_login" VALUES (6,'sdf',85.0,'dsd','2013-03-27');
INSERT INTO "main"."aws_login" VALUES (7,8.23,'sd','2013-03-26');
INSERT INTO "main"."aws_login" VALUES (8,23.3,'2013-03-28');
创建表后，插入数据。
接下来我们要按周查询数据
select '星期'||strftime('%w',lastTime),sum(password) FROM aws_login WHERE strftime('%W',lastTime)=strftime('%W','2013-03-30') GROUP BY strftime('%w',lastTime);
结果是
星期2 8.23
星期3 85.0
星期4 257.3
星期5 22.0
星期6 22.0
接下来是按月进行统计
select strftime('%m',lastTime)||'月',sum(password) FROM aws_login WHERE strftime('%m',lastTime)=strftime('%m','2013-03-30') GROUP BY strftime('%m',lastTime)
