如何在Mysql中做到这一点,让所有用户在所选日期范围内甚至没有记录或缺席?
考勤_tbl
ID | user_ID | time_intime_out | created_at | |
---|---|---|---|---|
1 | 001 | 2022-01-01 08:00:00 | 2022:01-01 17:00:00 | 2020 2-01-03 08:00:00 |
2 | 002 | 2022-01-01 08:15:24 | 2022:01-03 08:15:24 | |
3 | 003 | 2022-01-02 08:44:55 | 2022:01-02 17:00:00 | |
004 | 2022-01-03 08:40:22 | 2022-01:03 17:00:00 | 2022:01-04 08:40:22 |
考虑到您想要包括缺失的数据,我们需要从获取所需期间的日期范围开始。使用用户变量来存储和增加计数器值是一种性能良好的方法-
SELECT
'2022-01-01' + INTERVAL @row_number DAY `date`,
@row_number := @row_number + 1
FROM `attendance_tbl`, (SELECT @row_number := 0) AS `x`
LIMIT 31 /* 31 days in January */
如果你有一个表有一个连续的整数序列(自动递增PK而不删除(,你可以用它来代替-
SELECT '2022-01-01' + INTERVAL (`id` - 1) DAY `date`
FROM `attendance_tbl`
WHERE `id` <= 31 /* 31 days in January */
ORDER BY `id` ASC
然后,我们添加一个交叉连接来构建完整的日期和用户集-
SELECT *
FROM (
SELECT '2022-01-01' + INTERVAL (`id` - 1) DAY `date`
FROM `attendance_tbl`
WHERE `id` <= 31
ORDER BY `id` ASC
) d
CROSS JOIN `users_tbl` `u`
通过这两个表之间的交叉连接,我们得到了笛卡尔乘积(两个集合的所有组合(。然后,我们只需进一步使用考勤数据的左联接-
SELECT
`u`.`user_id`,
DATE_FORMAT(`d`.`date`, '%b %e %Y') `date`,
`u`.`f_name`,
`a`.`time_in`,
`a`.`time_out`
FROM (
SELECT
'2022-01-01' + INTERVAL (`id` - 1) DAY `date`,
(SELECT TIMESTAMP(`date`, '00:00:00')) `begin`,
(SELECT TIMESTAMP(`date`, '23:59:59')) `end`
FROM `attendance_tbl`
WHERE `id` <= 31
ORDER BY `id` ASC
) d
CROSS JOIN `users_tbl` `u`
LEFT JOIN `attendance_tbl` `a`
ON `u`.`user_id` = `a`.`user_id`
AND `a`.`time_in` BETWEEN `d`.`begin` AND `d`.`end`
ORDER BY `d`.`date`, `u`.`user_id`
如果您的attendance_tbl
每个用户每天可以有超过1行,那么您需要在选择列表中添加GROUP BY d.date, u.user_id
和aggregate_functions。
我已将begin
和end
添加到派生表中。这是为了允许联接使用索引。当attendance_tbl
很小时,这并不重要,但随着表的增长,这将更加重要。从长远来看,在(user_id,time_in(上添加索引将对性能产生巨大影响。
这是一个数据库<gt;小提琴给你玩。
要使用PDO从PHP运行这个,你可以做这样的事情-
<?php
$pdo = new PDO($dsn, $user, $password);
$sql = "SELECT
`u`.`user_id`,
DATE_FORMAT(`d`.`date`, '%b %e %Y') `date`,
`u`.`f_name`,
`a`.`time_in`,
`a`.`time_out`
FROM (
SELECT
:START_DATE + INTERVAL (`id` - 1) DAY `date`,
(SELECT TIMESTAMP(`date`, '00:00:00')) `begin`,
(SELECT TIMESTAMP(`date`, '23:59:59')) `end`
FROM `attendance_tbl`
WHERE `id` <= :DAYS_RANGE
ORDER BY `id` ASC
) d
CROSS JOIN `users_tbl` `u`
LEFT JOIN `attendance_tbl` `a`
ON `u`.`user_id` = `a`.`user_id`
AND `a`.`time_in` BETWEEN `d`.`begin` AND `d`.`end`
ORDER BY `d`.`date`, `u`.`user_id`";
$stmt = $pdo->prepare($sql);
$startDate = new DateTime('2022-01-01');
$endDate = new DateTime('2022-02-01');
$interval = $startDate->diff($endDate, true);
$daysRange = $interval->days + 1;
// Execute the statement
$stmt->execute([
':START_DATE' => $startDate->format('Y-m-d'),
':DAYS_RANGE' => $daysRange]
);
$attendance = $stmt->fetchAll(PDO::FETCH_OBJ);
检查此项。在这里,我调用attendance_tbl
两次,一次用于创建日期和用户列表,另一次用于获取数据(时间输入和时间输出(。通过使用BETWEEN
作为@nnicholls建议的过滤您喜欢的选定范围,我刚才刚刚意识到了这一点。
select u.`user_id`, date(a.time_in) as `date`, u.`f_name`, b.`time_in`, b.`time_out`, b.created_at from attendance_tbl a
join users_tbl u
left join attendance_tbl b on b.`user_id`=u.`user_id` and date(b.`time_in`)=date(a.`time_in`)
WHERE DATE(a.time_in) BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY `date`, u.user_id;
结果
user_id date f_name time_in time_out created_at
------- ---------- ----------- ------------------- ------------------- ---------------------
001 2022-01-01 John Doe 2022-01-01 08:00:00 2022-01-01 17:00:00 2022-01-03 08:00:00
002 2022-01-01 Jane Doe 2022-01-01 08:15:24 2022-01-01 17:00:00 2022-01-03 08:15:24
003 2022-01-01 Ronal Black (NULL) (NULL) (NULL)
004 2022-01-01 Lucy White (NULL) (NULL) (NULL)
001 2022-01-02 John Doe (NULL) (NULL) (NULL)
002 2022-01-02 Jane Doe (NULL) (NULL) (NULL)
003 2022-01-02 Ronal Black 2022-01-02 08:44:55 2022-01-02 17:00:00 2022-01-04 08:44:55
004 2022-01-02 Lucy White (NULL) (NULL) (NULL)
001 2022-01-03 John Doe (NULL) (NULL) (NULL)
002 2022-01-03 Jane Doe (NULL) (NULL) (NULL)
003 2022-01-03 Ronal Black (NULL) (NULL) (NULL)
004 2022-01-03 Lucy White 2022-01-03 08:40:22 2022-01-03 17:00:00 2022-01-04 08:40:22
对于ID
列,只需创建一个具有AUTO_INCREMENT
id的表并插入您选择的数据。
要像示例结果中那样格式化日期(如果确实需要的话(,只需将DATE(a.time_in)
更改为DATE_format(a.time_in, '%b %d %Y')
即可。
SQL Fiddle示例