MySQL查询以获取未分配日期的非数据 [英] MySQL query to get non-data for unassigned dates

问题描述

我有一个表格，每周的数据如下：

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

目前我有一个PHP脚本，首先获取日期范围（星期一到星期五），然后对该日期范围内的每个用户ID进行查询。然后，我循环浏览结果并检查日期之间的差距。如果有差距，它输出日期，网站列为保持家。它必须连续几天（例如，如果用户只在周一和周五驻留），并且它在每周结束时有一个额外的步骤，以确保如果用户在家后星期三（因此有星期四和星期五填补作为差距）。所以有三个检查，它必须做，一个用户不是从星期一开始，一个用于天之间的差距，一个用户结束在星期五之前...加上它必须在任何情况下，连续天数。

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.

脚本基本上创建了一个检查日期范围的数组，并且已经工作了好几个月。 HOWEVER

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

我想知道，是否有一个MySQL函数为每个用户返回日期？所以我可以在查询中填写保持家，而不必运行一个有趣的脚本，永远解释给我的脚本上与我一起工作的其他人。

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?

谢谢！

推荐答案

轻松！
翻转查询！

Easy! Flip the query around!

这是：

1. 每周查找表


2. 雕刻一个查询，在查找表和您的表DAYNAME（字段）上执行LEFT_OUTER_JOIN。也就是说，它将返回查找表中的所有行，并且只返回在您的表中匹配的所有行,,,

确定是否要按人员过滤。条件将如下所示： userid ='person'or userid is null

I'm not sure if you want to filter by person .. the criteria would look like userid = 'person' or userid is null

1. 所有日期的ROW


2. 在行中，UserID将存在对于非空白天，对于跳过的天数为空。

这篇关于MySQL查询以获取未分配日期的非数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！