我有一个表，其中包含不同员工在不同日期的时间条目，以及记录他们时间的事件。我想找到他们在同一事件上花费最少时间的所有行，例如，在 3 天的时间段内。

这是我要查询的表格的简化版本:

CREATE TABLE `time_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `work_date` date NOT NULL,
  `time_spent` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Time, in minutes, spent on the current activity',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

和一些示例数据:

+----+-------------+-------------+------------+------------+
| id | employee_id | activity_id | work_date  | time_spent |
+----+-------------+-------------+------------+------------+
| 10 |           1 |           2 | 2016-06-11 |        120 |
| 16 |           1 |           3 | 2016-06-21 |        450 |
| 29 |           1 |           4 | 2016-06-22 |        450 |
| 17 |           1 |           4 | 2016-06-23 |        450 |
| 12 |           3 |           4 | 2016-06-23 |        450 |
|  4 |           1 |           4 | 2016-06-24 |        450 |
| 22 |           1 |           4 | 2016-06-26 |         60 |
|  9 |           1 |           6 | 2016-06-27 |        450 |
+----+-------------+-------------+------------+------------+

time_spent 以分钟为单位，我基本上想选择所有构成至少 3 天的 block 的行，time_spent = n 天* 在相同的 activity_id 和 employee_id

在上面的示例中，我想检索第 29、17、4 行。不会包含第 16 行，因为这是一个不同的 activity_id，第 12 行也不会，因为这是一个不同的员工编号。第 22 行缺少日期，因此会“中断”日期。

我想我可以创建一个 View 或临时表来给我一个日期序列，并使用一些聚合函数根据SUM(time_spent) 其中工作日期介于给定日期和 work_date + 3 天

这并不是我以前必须实现的目标，但考虑一下，可能会成为 future 分析的有用工具。

最佳答案

使用您和我的测试数据的以下架构:

架构

CREATE TABLE `time_entries` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `employee_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `work_date` date NOT NULL,
  `time_spent` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Time, in minutes, spent on the current activity'
) ENGINE=InnoDB;

测试数据

请注意，为了简化构建测试数据，我使用自动增量并允许数据库分配 id。而不是直接插入 id。我确实在下面的最右边显示了 ID 号，例如 -- 7

insert time_entries(employee_id,activity_id,work_date,time_spent) values
(1,2,'2016-06-11',120), --  1
(1,3,'2016-06-21',450), --  2
(1,13,'2016-06-21',450), -- 3
(1,14,'2016-06-21',450), -- 4
(1,15,'2016-06-21',450), -- 5
(1,4,'2016-06-22',450), -- 6
(1,4,'2016-06-23',450), -- 7
(3,4,'2016-06-23',450), -- 8
(1,4,'2016-06-24',450), -- 9
(1,16,'2016-06-25',450), -- 10
(1,17,'2016-06-25',450), -- 11
(1,4,'2016-06-26',60), -- 12
(1,6,'2016-06-27',450), -- 13
(3,4,'2016-06-27',450), -- 14
(3,4,'2016-06-28',450), -- 15
(3,4,'2016-06-29',450), -- 16
(4,4,'2016-06-28',200), -- 17
(4,4,'2016-06-29',200), -- 18
(4,4,'2016-06-30',200), -- 19
(4,4,'2016-07-01',200), -- 20
(4,4,'2016-07-03',200), -- 21
(5,4,'2016-07-08',200), -- 22
(5,4,'2016-07-09',200), -- 23
(5,4,'2016-07-10',200), -- 24
(5,4,'2016-07-12',200), -- 25
(5,4,'2016-07-13',200), -- 26
(5,4,'2016-07-14',200), -- 27
(5,4,'2016-07-15',200), -- 28
(6,6,'2016-08-01',500), -- 29
(6,6,'2016-08-02',500), -- 30
(6,6,'2016-08-04',500), -- 31
(6,6,'2016-08-05',500), -- 32
(7,6,'2016-08-21',500), -- 33
(7,6,'2016-08-22',500), -- 34
(7,6,'2016-08-23',500), -- 35
(7,6,'2016-08-25',500), -- 36
(7,6,'2016-08-26',500); -- 37

最终查询

select distinct t4.id,t4.employee_id,t4.activity_id,t4.work_date,t4.time_spent
from time_entries t4
join
(   select t3.id,t3.employee_id,t3.activity_id,t3.work_date
    from time_entries t3
    join
    (   select t1.id,count(*) as rowcount,sum(t2.time_spent) as timeworked
        from time_entries t1
        join time_entries t2
        on t2.employee_id=t1.employee_id 
        and t2.activity_id=t1.activity_id 
        and datediff(t2.work_date,t1.work_date)<=2
        and t2.work_date>=t1.work_date
        group by t1.id
        having rowcount=3 and timeworked>=450
    ) xDerived1
    on t3.id=xDerived1.id
) xDerived2
on t4.employee_id=xDerived2.employee_id 
and t4.activity_id=xDerived2.activity_id
and datediff(t4.work_date,xDerived2.work_date)<=2
and datediff(t4.work_date,xDerived2.work_date)>=0
order by t4.employee_id,t4.activity_id,t4.work_date;

结果

+----+-------------+-------------+------------+------------+
| id | employee_id | activity_id | work_date  | time_spent |
+----+-------------+-------------+------------+------------+
|  6 |           1 |           4 | 2016-06-22 |        450 |
|  7 |           1 |           4 | 2016-06-23 |        450 |
|  9 |           1 |           4 | 2016-06-24 |        450 |
| 14 |           3 |           4 | 2016-06-27 |        450 |
| 15 |           3 |           4 | 2016-06-28 |        450 |
| 16 |           3 |           4 | 2016-06-29 |        450 |
| 17 |           4 |           4 | 2016-06-28 |        200 |
| 18 |           4 |           4 | 2016-06-29 |        200 |
| 19 |           4 |           4 | 2016-06-30 |        200 |
| 20 |           4 |           4 | 2016-07-01 |        200 |
| 22 |           5 |           4 | 2016-07-08 |        200 |
| 23 |           5 |           4 | 2016-07-09 |        200 |
| 24 |           5 |           4 | 2016-07-10 |        200 |
| 25 |           5 |           4 | 2016-07-12 |        200 |
| 26 |           5 |           4 | 2016-07-13 |        200 |
| 27 |           5 |           4 | 2016-07-14 |        200 |
| 28 |           5 |           4 | 2016-07-15 |        200 |
| 33 |           7 |           6 | 2016-08-21 |        500 |
| 34 |           7 |           6 | 2016-08-22 |        500 |
| 35 |           7 |           6 | 2016-08-23 |        500 |
+----+-------------+-------------+------------+------------+
20 rows in set (0.00 sec)

大约一半的行符合条件。根据“显示......行”的要求，它可以显示行，如果连续 4 天(对于给定的 worker /事件/小时总和)，则可能出现超过 3 天结果。意思是，如果有 4 个 block ，则前 3 个有资格，最后 3 个有资格。结果显示了这一点。

对于 xDerived1 的可视化，请参见以下内容:

关于mysql - 如何根据相邻日期的值之和查找 MySQL 行？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/37683429/