DROP TABLE "main"."aws_login";
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, 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, 22.0, 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, 'sdf', 8.23, 'sd', '2013-03-26');
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, 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, 22.0, 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, 'sdf', 8.23, 'sd', '2013-03-26');
INSERT INTO "main"."aws_login" VALUES (8, 'sdf', 23.3, 0, '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)