是某些时期内的日期 [英] Is a date within some of periods

问题描述

我有以下项目及其活动期间的表格(期间用 FROM 和 TO 日期定义):

I have the following table of projects with their activity periods (periods are defined with FROM and TO dates):

ID | ProjID | ActiveFrom | ActiveTo
===+========+============+============
 1 |     20 | 2018-01-01 | 2018-01-20
 2 |     20 | 2018-02-05 | 2018-02-12
 3 |     20 | 2018-02-20 | 2018-02-27
 4 |     30 | 2018-01-15 | 2018-02-15

当然，一个项目可以有任意数量的活动期.

Of course, a project can have an arbitrary number of activity periods.

我需要一个 SQL 查询(函数)，如果给定项目在某个给定日期(在某些项目的活动期间内给定日期)处于活动状态，它将返回 true/false.

I need a SQL query (function) which will return true/false if a given project was active on some given date (is given date within some of project's activity periods).

推荐答案

这个函数应该可以做你想做的.它依赖于 MySQL 在数字上下文中将布尔结果视为 1 或 0，因此 MAX 调用有效地变成了所有条件的 OR.

This function should do what you want. It relies on MySQL treating boolean results as either 1 or 0 in a numeric context, thus the MAX call effectively becomes an OR of all the conditions.

CREATE FUNCTION check_activity(project_id INT, check_date DATE)
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
  RETURN (SELECT MAX(check_date BETWEEN ActiveFrom AND ActiveTo) FROM projects WHERE ProjId = project_id);
END
SELECT check_activity(20, '2018-01-10'), check_activity(20, '2018-02-01')

输出

check_activity(20, '2018-01-10')    check_activity(20, '2018-02-01')
1                                   0

dbfiddle 演示

这篇关于是某些时期内的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！