mysql - 用于在给定时间点生成正在进行的工作的 SQL 查询

Question

我有一个项目管理数据库，我正在尝试为其构建一个 SQL 查询，该查询将生成给定用户在给定时间点正在进行的工作的最新估计。

正在进行的工作被定义为一项任务，在给定时间，其最近的任务历史记录是状态列为以下值之一的任务:12,13,14,15 .

下面提供了结构和数据集的摘录。

对于此数据集，适用以下场景:

场景 1

• 输入参数:
  • 用户 ID:1
  • 时间点:2018-01-01
• 应返回 ID 为 1 的估算条目
  • 乐观:5，现实 10，悲观:20

场景 2

• 输入参数:
  • 用户 ID:1
  • 时间点:2017-01-01
• 应返回 ID 为 4 的估算条目
  • 乐观:2、现实:4、悲观:6

场景 3

• 输入参数
  • 用户 ID:1
  • 时间点:2017-06-01
• 不应返回任何条目 - 此时用户 1 没有正在进行的工作

场景 4

• 输入参数
  • 用户 ID:2
  • 时间点:2017-01-01
• 不应返回任何条目 - 用户 2 此时(或任何其他时间)没有正在进行的工作

这个查询的复杂性远远超出了我通常必须处理的 SQL 范围。因此，我正在寻找有关如何构建查询的建议。请告知我是否应该在其他地方请求此类帮助。

# Dump of table estimate
# ------------------------------------------------------------

DROP TABLE IF EXISTS `estimate`;

CREATE TABLE `estimate` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `task_id` int(11) DEFAULT NULL,
  `optimistic` float DEFAULT NULL,
  `realistic` float DEFAULT NULL,
  `pessimistic` float DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `estimate` WRITE;
/*!40000 ALTER TABLE `estimate` DISABLE KEYS */;

INSERT INTO `estimate` (`id`, `task_id`, `optimistic`, `realistic`, `pessimistic`, `created`)
VALUES
    (1,1,5,10,20,'2017-10-01 00:00:00'),
    (2,1,10,20,30,'2018-02-01 00:00:00'),
    (3,2,1,2,3,'2016-10-01 00:00:00'),
    (4,2,2,4,6,'2016-11-01 00:00:00'),
    (5,2,3,6,0,'2017-01-01 00:00:00');

/*!40000 ALTER TABLE `estimate` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table task
# ------------------------------------------------------------

DROP TABLE IF EXISTS `task`;

CREATE TABLE `task` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `task` WRITE;
/*!40000 ALTER TABLE `task` DISABLE KEYS */;

INSERT INTO `task` (`id`, `user_id`, `name`)
VALUES
    (1,1,'WIP 2018-01-01'),
    (2,1,'WIP 2017-01-01');

/*!40000 ALTER TABLE `task` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table task_history
# ------------------------------------------------------------

DROP TABLE IF EXISTS `task_history`;

CREATE TABLE `task_history` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `task_id` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `task_history` WRITE;
/*!40000 ALTER TABLE `task_history` DISABLE KEYS */;

INSERT INTO `task_history` (`id`, `task_id`, `status`, `timestamp`)
VALUES
    (1,1,10,'2017-10-01 00:00:00'),
    (2,1,12,'2017-12-01 00:00:00'),
    (3,1,1,'2018-01-03 00:00:00'),
    (4,2,10,'2016-10-01 00:00:00'),
    (5,2,14,'2016-12-01 00:00:00'),
    (6,2,1,'2017-02-01 00:00:00');

/*!40000 ALTER TABLE `task_history` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table user
# ------------------------------------------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;

INSERT INTO `user` (`id`, `name`)
VALUES
    (1,'User 1'),
    (2,'User 2');

/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;

Answer 1

SELECT *
FROM Estimate, TaskHistory
WHERE Estimate.task_id = TaskHistory.task_id
AND status IN (12,13,14,15);

这应该选择相应任务历史记录状态为 12、13、14 或 15 的估计。