主页 / user-224333

linux478's questions

Martin Hope
linux478
Asked: 2021-02-20 06:07:32 +0800 CST
  • 1

我使用的代码在这里

CREATE TABLE timecard (
   id INTEGER PRIMARY KEY autoincrement,
   clock_in REAL not null,
   clock_out REAL,
   lunch integer,
   pay_type TEXT
);
INSERT INTO timecard VALUES(1,julianday("2021-02-15 08:00"),julianday("2021-02-15 16:00"),0,"HOLIDAY");
INSERT INTO timecard VALUES(2,julianday("2021-02-16 07:23"),julianday("2021-02-16 16:49"),1,"WORK");
INSERT INTO timecard VALUES(3,julianday("2021-02-17 07:16"),julianday("2021-02-17 16:05"),1,"WORK");
INSERT INTO timecard VALUES(4,julianday("2021-02-18 07:54"),julianday("2021-02-18 15:48"),1,"WORK");

.header on
.mode column

SELECT strftime('%W',clock_in) AS 'weekofyear',
       time(clock_in) as "IN",
       time(clock_out) as "OUT",
       CASE when lunch = 1 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 - .5 As REAL),2)
            when lunch = 0 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 As REAL),2)
       END as "total_time",
       sum("total_time")
FROM timecard
GROUP BY "weekofyear";

这是命令的输出。

weekofyear  IN          OUT         total_time  sum("total_time")
----------  ----------  ----------  ----------  -----------------
07          08:00:00    16:00:00    8.0         0.0

我正在尝试获取一年中一周的总小时数。不确定我做错了什么。我对 SQL 和 SQL 中的 case 语句很陌生。

谢谢你的时间

sqlite case