sqlite中按周显示数据

发布时间：2020-12-12 20:19:32 所属栏目：百科来源：网络整理

导读：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_lo

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,'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)

（编辑：李大同）

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