首页 文章

SQL：一个月内的子间隔

提问于
浏览 次
1

我有一张 table hirefire . 下面是它的简化结构：

  • hired 日期

  • fired 日期

  • firereason smallint

我花了几个小时为这个表编写查询来解决问题，我的问题（简化）如下所示：

该表描述了员工何时开始工作以及何时去度假 .

来自此表的一行的间隔 hired..fired （包含 hired 且 fired 未包含在间隔中）称为"hire intervals" . 我保证雇用间隔不会互相覆盖，并且每行都是 fired>=hired .

我调用"fire intervals"所有间隔 fired1..hired2 其中 fired1 是此表中 r1 行的字段 fired 而 hired2 是此表的下一行 r2 中的字段 hired ，其中行按字段 hired 排序 . 对于每个这样的间隔，它被分配一个"fire reason"（对包含离开工作的理由的表的主要ID的引用，例如休假，育儿假，死亡等）等于 r1 中的字段 firereason .

让我们给出一个月（通过包含该月第一天的SQL DATE变量） .

我需要一组与给定月份相同的非空交叉点 . （这是我需要关于这些间隔的信息，这些间隔与给定月份至少有一天相同 . ）

MySQL PHP

mysql sql database

1 回答

  • 2

    所以你必须找到所有[解雇，雇用]间隔 .

    我的方法是首先选择给定范围内的所有"fired date"（在所需间隔的开始和结束时合成假发射日期） . 然后为雇用日期做同样的事情 - 最后通过配对匹配它们 .

    作为一张 Value 1000字的图片，图形上是查询的工作原理：
    A query to find values not in range

    这导致了一个相当复杂的查询（并且大多数效率低下 - 可能需要几个临时表文件）：

    SELECT * FROM
( -- Keep numbered list of "fired" date
SELECT (@i := @i+1) as n, F.* FROM (
  SELECT @start AS fired 
  UNION SELECT fired FROM hirefire
    WHERE fired > @start and hired < @end
  UNION SELECT @end
  ) AS F
   JOIN (SELECT @i := 0) AS init -- initialize @i
   ORDER BY F.fired ASC
) AS F
JOIN
( -- Keep numbered list of "hired" date
SELECT (@j := @j+1) as n, H.* FROM (
  SELECT @start AS hired 
  UNION SELECT hired FROM hirefire
    WHERE fired > @start and hired < @end
  UNION SELECT @end
  ) AS H
   JOIN (SELECT @j := 0) AS init -- initialize @j
   ORDER BY H.hired ASC
) AS H
ON( F.n+1 = H.n )
WHERE H.hired <> F.fired;

    有关实例，请参阅http://sqlfiddle.com/#!2/a841d0/39

    举个例子：

    create table hirefire(pk serial, hired int, fired int);
insert into hirefire(hired, fired) values
 (1,3), (5,10), (12,14), (16,25);
SET @start = 4;
SET @end = 30;

    会产生

    +----+--------+-------+
| N  | FIRED  | HIRED |
+----+--------+-------+
| 1  |     4  |     5 |
| 2  |    10  |    12 |
| 3  |    14  |    16 |
| 4  |    25  |    30 |
+----+--------+-------+

    还有一些解释的话：

    • 如您所见，我使用用户定义的变量对行进行编号（需要通过对匹配它们）

    • 我用 JOIN (SELECT @j := 0) 技巧初始化这些变量而不需要单独的 SET ... 语句

    • 我通过使用整数范围简化了这里的问题，以便通过减少"noise"来理解答案 . 您将不得不为 DATETIME 进行调整 .

    • 我使用"pure SQL"来查找最初需要的答案，但由于基于"row numbers"存在一些匹配，因此在应用程序级别解决该部分问题可能是最有效的;）

    这里仅供参考我的原始答案 . 它通过发出[雇用，解雇]间隔来产生正确查询的补充 .

    假设您有间隔[@start @end）

    SELECT DISTINCT GREATEST(@start, hired), LEAST(@end, fired)
 FROM hirefire
 WHERE @start < fired AND @end >= hired;

    我不太确定不平等/严格不平等的事情，但这就是精神 .

    有关示例，请参阅http://sqlfiddle.com/#!2/a841d0/7 . 它使用普通整数来定义范围，但我认为你可以在没有太多努力的情况下将其调整到 DATETIME .

    回复于

相关问题