我目前正在尝试编写一个查询,该查询将返回在我们的时间表系统中没有记录给定日期时间的所有用户 . 我们目前有2个表,时间表和用户 . 我正在尝试进行一个查询,该查询将返回日期范围的时间表表中没有条目的用户列表 . 每天的时间表中只有一条记录,这应该很简单,但我无法弄清楚如何在我的生活中接近这一点 .
任何帮助,将不胜感激 :) .
+-----------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+------------------+------+-----+---------+----------------+
| timesheetID | int(11) unsigned | NO | PRI | NULL | auto_increment |
| timesheetForUser | int(11) unsigned | NO | | | |
| timesheetForDate | date | NO | | | |
| timesheetForCheckIn | int(11) | YES | | NULL | |
| timesheetNotes | text | YES | | NULL | |
| timesheetIsFilled | tinyint(1) | NO | | | |
| timesheetNoFillReason | int(11) unsigned | NO | | | |
| timesheetCreatedOn | datetime | NO | | | |
| timesheetCreatedBy | int(11) unsigned | NO | | | |
| timesheetUpdatedOn | datetime | YES | | NULL | |
| timesheetUpdatedBy | int(11) unsigned | YES | | NULL | |
+-----------------------+------------------+------+-----+---------+----------------+
+--------------------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------------+---------------+------+-----+---------+----------------+
| userID | int(11) | NO | PRI | NULL | auto_increment |
| userAccount | int(11) | YES | | NULL | |
| userOrganization | int(11) | YES | | NULL | |
| userIsEmployee | tinyint(4) | YES | | 0 | |
| userEmployeeSince | date | YES | | NULL | |
| userName | varchar(255) | YES | | NULL | |
| userTitle | varchar(255) | YES | | NULL | |
| userEmail | varchar(255) | YES | | NULL | |
| userLogin | varchar(50) | YES | | NULL | |
| userPassword | varchar(255) | YES | | NULL | |
| userSendInvitation | tinyint(4) | YES | | NULL | |
| userAddress1 | varchar(255) | YES | | NULL | |
| userAddress2 | varchar(255) | YES | | NULL | |
| userCity | varchar(255) | YES | | NULL | |
| userCountry | char(2) | YES | | NULL | |
| userState | varchar(6) | YES | | NULL | |
| userStateOther | varchar(255) | YES | | NULL | |
| userZip | varchar(20) | YES | | NULL | |
| userPhone | varchar(50) | YES | | NULL | |
| user_easypaycode | varchar(6) | YES | | NULL | |
| userFax | varchar(50) | YES | | NULL | |
| userCell | varchar(50) | YES | | NULL | |
| userTimezone | int(11) | YES | | NULL | |
| userNotes | text | YES | | NULL | |
| userActive | tinyint(4) | NO | | 0 | |
| userDisplayPictureType | tinyint(4) | YES | | NULL | |
| userDisplayPicture | varchar(255) | YES | | NULL | |
| userThumbnailPicture | varchar(255) | YES | | NULL | |
| userCanWriteMessages | tinyint(4) | NO | | 0 | |
| userCanWriteComments | tinyint(4) | NO | | 0 | |
| userCanUploadFiles | tinyint(4) | NO | | 0 | |
| userCanCreateEvents | tinyint(4) | NO | | 0 | |
| userCanCreateTickets | tinyint(4) | NO | | 0 | |
| userCanManageProjects | tinyint(4) | NO | | 0 | |
| userCanManageUsers | tinyint(4) | NO | | 0 | |
| userCanManageOrganizations | tinyint(4) | NO | | 0 | |
| userCanManageUserGroups | tinyint(4) | NO | | 0 | |
| userCanManageMessageCategories | tinyint(4) | NO | | 0 | |
| userCanManageSetupOptions | tinyint(4) | NO | | 0 | |
| userCanManageAllUsersItems | tinyint(4) | NO | | 0 | |
| userCanEnterTimesheets | tinyint(4) | NO | | | |
| userCanManageTimesheets | tinyint(4) | NO | | | |
| userCanUseTimeclock | tinyint(4) | YES | | NULL | |
| userCanOnlyUseTimeclock | tinyint(4) | YES | | NULL | |
| userLastLogin | datetime | NO | | | |
| userPWResetText | varchar(255) | YES | | NULL | |
| userDeleted | tinyint(4) | NO | | 0 | |
| userDeletedBy | int(11) | YES | | NULL | |
| userDeletedOn | datetime | YES | | NULL | |
| userMinHoursPerDay | decimal(10,1) | YES | | NULL | |
+--------------------------------+---------------+------+-----+---------+----------------+
此外,如果一天没有记录时间,则在时间表中不会创建记录 .
3 回答
第一个查询获取@start和@end之间没有注册的所有用户:
此查询将提取所有缺少任何日期和缺少日期的用户(正如您在问题评论中所要求的那样):
试验台:
使用测试台的第二次查询输出:
如果需要,您还可以创建第二个查询作为视图,并像这样查询:
你可以试试