我想構建一個特定的日歷功能。
作為地下室，我有以下 MySQL 表：
CREATE TABLE `presence` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`eid` int(10) unsigned DEFAULT NULL,
`start` timestamp NULL DEFAULT NULL,
`end` timestamp NULL DEFAULT NULL,
`frequence` int(11) DEFAULT NULL,
`fulltime` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
使用以下示例資料：
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (1, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", NULL, NULL);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (2, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", 1, NULL);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (3, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", 1, 1);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (4, 14, "2021-11-29 10:00:00", "2021-12-13 18:00:00", NULL, 1);
INSERT INTO `presence` (`id`, `eid`, `start`, `end`, `frequence`, `fulltime`) VALUES (5, 14, "2021-11-29 10:00:00", NULL, 1, 1);
在我的應用程式環境中，我有以下示例：
行 id#1表示從 2021-11-29 到 2021-12-13 每天都有一名員工（eid：14）在場。每天上午 10 點至下午 6 點。
行 id#2表示從 2021-11-29 到 2021-12-13 的每個星期一都有一名員工（eid：14）在場。每周一上午 10 點至下午 6 點。星期一因為start是星期一，因為frequence = 1。
行 id#3表示從 2021-11-29 到 2021-12-13 的每個星期一都有一名員工（eid：14）在場。每個星期一一整天，因為fullday = 1。因此，忽略了時間。
行 id#4表示從 2021-11-29 到 2021-12-13 每天都有一名員工（eid：14）在場。每天一整天，因為fullday = 1再講不理時間。
行 id#5終于有一個開放的存在。這意味著該員工從 2021 年 11 月 29 日到無窮大每周一都在場。
我一整天都在處理以下查詢，現在我無法說出我的期望......
SET @date = "2021-12-13 19:00:00";
SET @fullday = 0;
SELECT start, end, frequence, fulltime
FROM `presence`
WHERE (
IF (@fullday = 1,
DATE(@date) >= DATE(`start`)
,
DATE(@date) >= DATE(`start`) AND
TIME(@date) >= TIME(`start`)
)
)
AND (
IF (@fullday = 1,
DATE(@date) >= DATE(`end`)
,
DATE(@date) >= DATE(`end`) AND
TIME(@date) >= TIME(`end`)
)
)
AND (
`frequence` IS NULL OR
`frequence` = 1 AND WEEKDAY(@date) = WEEKDAY(`start`)
)
在我的應用程式中，我將有一個 php 函式employee_is_present(int $eid, string $date): bool，其中$date可以是Y-m-d H:i:s或者只是Y-m-d，以檢查員工是否在一天（沒有時間）或非常特定的時間通常有空。這就是我試圖模仿的SET @fullday = 0|1;
uj5u.com熱心網友回復：
根據輸入，date可以有時間也可以沒有時間，比較容易區分這兩種情況。我們可以這樣做，因為我們使用函式呼叫來查詢
檢查日期只能使用此查詢
SET @eid =14;
SET @date = DATE("2021-12-13");
SELECT *
FROM presence
WHERE @date >= DATE(`start`) AND ( `end` IS NULL OR @date <= DATE(`end`))
AND (`frequence` IS NULL OR (`frequence` = 1 AND WEEKDAY(`start`) = WEEKDAY(@date)))
AND eid = @eid
檢查時間，我們使用上面的查詢結果過濾時間
SET @eid =14;
SET @date = DATE("2021-12-13 19:00:00");
SET @time = TIME(@date);
SELECT *
FROM (
SELECT *
FROM presence
WHERE @date >= DATE(`start`) AND ( `end` IS NULL OR @date <= DATE(`end`))
AND (`frequence` IS NULL OR (`frequence` = 1 AND WEEKDAY(`start`) = WEEKDAY(@date)))
AND eid = @eid
) f
WHERE `fulltime` = 1 OR (TIME(`start`) <= @time AND @time <= TIME(`end`))
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3e207fed77e34a6b458e79764e382156
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/349047.html