MySQL查询获取未分配日期的非数据

发布于 2024-07-27 01:42:42 字数 807 浏览 8 评论 0原文

我有一个包含每周数据的表，如下所示：

userID       Site             date
------      ------           ------
Smith    Ferris Wheel       2009-07-13
Jones   Outerspaceland      2009-07-13
LChar   Ferris Wheel       2009-07-14
Smith     Underworld        2009-07-16
Jones     Fish Bowl         2009-07-17
Munson    Go-Go Tech        2009-07-16

目前，我有一个 PHP 脚本，它首先获取日期范围（星期一 - 星期五），然后对该日期范围的每个用户 ID 进行查询。然后，我循环查看结果并检查日期之间的差距。如果存在差距，它会输出日期，并且该站点将被列为“待在家里”。它必须考虑连续几天（例如用户仅在该周的周一和周五驻扎），并且必须在每周结束时执行额外的步骤，以确保用户是否在之后呆在家里星期三（因此有星期四和星期五作为空白）。因此，它必须进行三项检查，一项针对未在周一开始的用户，一项针对日期之间的间隔，一项针对周五之前结束的用户......此外，它还必须考虑到任何这些情况的连续天数。

该脚本基本上创建了一个日期范围数组来检查，并且几个月来它一直运行良好。然而

我想知道，是否有一个 MySQL 函数可以为每个用户返回休息日？这样我就可以让它在查询中填写“待在家里”，而不必运行一个需要永远向与我一起处理脚本的其他人解释的时髦脚本？

谢谢！

原文

I have a table with weekly data that looks like:

userID       Site             date
------      ------           ------
Smith    Ferris Wheel       2009-07-13
Jones   Outerspaceland      2009-07-13
LChar   Ferris Wheel       2009-07-14
Smith     Underworld        2009-07-16
Jones     Fish Bowl         2009-07-17
Munson    Go-Go Tech        2009-07-16

Currently I have a PHP script that first gets the date range (Monday -- Friday), then does a query for each userID for that date range. Then, I loop through the results and check for gaps between dates. If there is gap, it outputs the date and the Site is listed as "Stay Home." It has to account for several days in a row (like if the user is only stationed on Monday and Friday that week), and it has to an extra step at the end of each week to make sure that if the user is staying home after Wednesday (thus having Thursday and Friday to fill in as gaps). So there are three checks it has to make, one for users not starting on Monday, one for gaps between days, and one for users ending before Friday...plus it has to account for days in a row for any of those situations.

The script basically creates an array of the date range to check against, and it has worked fine for months. HOWEVER

I was wondering, is there a MySQL function to have days off returned for each user? So that I can have it fill in "Stay Home" in the query and not have to run a funky script that takes forever to explain to other people working with me on my script?

Thanks!

分享到QQ

分享到微博