获取数据库最新的一条打卡时间数据SQL语句

获取数据库最新的一条打卡时间数据SQL语句

上班表创建SQL语句

CREATE TABLE "PunchIn" (
	"id" INT NOT NULL,
	"emp_code" VARCHAR(50) NOT NULL COLLATE 'Chinese_PRC_CI_AS',
	"punch_in_time" DATETIME NOT NULL,
	"status" VARCHAR(10) NOT NULL COLLATE 'Chinese_PRC_CI_AS',
	PRIMARY KEY ("id")
)
;

下班打卡时间表创建SQL语句

CREATE TABLE "PunchOut" (
	"id" INT NOT NULL,
	"emp_code" VARCHAR(50) NOT NULL COLLATE 'Chinese_PRC_CI_AS',
	"punch_out_time" DATETIME NOT NULL,
	"status" VARCHAR(10) NOT NULL COLLATE 'Chinese_PRC_CI_AS',
	PRIMARY KEY ("id")
)
;

员工信息表创建SQL语句

CREATE TABLE "Employee" (
	"id" INT NOT NULL,
	"emp_code" VARCHAR(50) NOT NULL COLLATE 'Chinese_PRC_CI_AS',
	"username" VARCHAR(50) NOT NULL COLLATE 'Chinese_PRC_CI_AS',
	"password" VARCHAR(50) NOT NULL COLLATE 'Chinese_PRC_CI_AS',
	PRIMARY KEY ("id")
)
;

⚠️注意在创建表时把这个去掉：COLLATE ‘Chinese_PRC_CI_AS’

实现获取最新的打卡下班时间SQL语句

SELECT * FROM (
SELECT a.id,
       a1.emp_code AS 工号, 
       a1.username AS 用户名,
       CONVERT(VARCHAR(19), a.punch_in_time, 120) AS 上班打卡时间, 
       a.status AS 上班打卡状态,
       CONVERT(VARCHAR(19), b.punch_out_time, 120) AS 下班打卡时间, 
       b.status AS 下班打卡状态
FROM Employee a1 
LEFT JOIN PunchIn a 
ON a1.emp_code = a.emp_code
LEFT JOIN PunchOut b 
ON a.emp_code = b.emp_code 
   AND CONVERT(DATE, a.punch_in_time) = CONVERT(DATE, b.punch_out_time)
   AND b.id = (
       SELECT MAX(id)
       FROM PunchOut
       WHERE emp_code = a.emp_code 
       AND CONVERT(DATE, punch_out_time) = CONVERT(DATE, a.punch_in_time)
   )
ORDER BY a.emp_code, a.punch_in_time
) main WHERE 下班打卡时间 IS NULL;