MySQL:IN子句的开始和结束日期之间的日期? [英] MySQL: IN clause for dates BETWEEN a start and end date?

问题描述

我有一个格式为YYYY-MM的日期列表，并且我有一个充满记录的表，其中包含starts_at和ends_at列.

I have list of dates in the format YYYY-MM, and I have a table full of records with a starts_at and ends_at column.

我要查找传入列表中任何给定日期在starts_at和ends_at范围内的所有记录.

I want to find all records for which any given date in the passed-in list is within the starts_at and ends_at range.

因此，考虑到2011-01, 2011-05, 2011-10，我想要:

So, given 2011-01, 2011-05, 2011-10, I want:

| id | starts_at           | ends_at             | title                             |
| 3  | 2010-12-05 00:00:00 | 2011-02-02 00:00:00 | something cool                    |
| 4  | 2011-03-14 00:00:00 | 2011-05-01 00:00:00 | something else really cool        |
| 5  | 2011-10-31 00:00:00 | 2012-12-23 00:00:00 | argh! end of the world! not cool! |

...而这些记录将被省略:

... while these records would be omitted:

| id | starts_at           | ends_at             | title                             |
| 6  | 2010-10-05 00:00:00 | 2010-12-02 00:00:00 | something uncool                  |
| 7  | 2011-03-14 00:00:00 | 2011-04-31 00:00:00 | something else really uncool      |
| 8  | 2011-12-23 00:00:00 | 2013-01-01 00:00:00 | yay! we're still alive! cool!     |

我该如何在SQL中编写此WHERE条件?

How would I write this WHERE condition in SQL?

澄清:我正在寻找纯SQL解决方案(我正在使用存储过程，因此到目前为止，通过PHP等其他语言进行动态注入实际上是不可能的我知道)，并且日期列表作为string(HTML表单输入)传递到查询中.如果可以在SQL中以编程方式进行操作，我希望将其分解为顺序的BETWEEN语句，但是我不知道如何执行该操作.

Clarification: I'm looking for a solution in pure SQL (I'm working with a stored procedure, so dynamic injection through some other language like PHP isn't really possible, so far as I know), and the list of dates is being passed in to the query as a string (HTML form input). I would love to break it down into sequential BETWEEN statements if I could do that programmatically in SQL, but I've no clue how to do that.

基本上，我需要一种在纯SQL中表达以下逻辑的方法:

Basically, I need a way to express the following logic in pure SQL:

$months = explode(',', '2011-01,2011-05,2011-10');
$q = "SELECT records.* FROM records WHERE";
foreach($months as $month) {
  $q .= " '$month' BETWEEN records.starts_at AND records.ends_at OR";
}
$q = substr($q, 0, -3) . ';';

推荐答案

SELECT id, starts_at, ends_at, title
FROM yourtable
WHERE '2011-01-01' BETWEEN starts_at AND ends_at
OR    '2011-05-01' BETWEEN starts_at AND ends_at
OR    '2011-10-01' BETWEEN starts_at AND ends_at

这篇关于MySQL:IN子句的开始和结束日期之间的日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！