其他日期之间的日期，不考虑年份 [英] Date between 2 other dates, disregarding year

问题描述

我正在寻找像之间的人在这里是寻找，只有我想使用MySQL。下面的表格是我在数据库中找到的东西（简化）。

I'm looking for something like the person here was lookin for, only I'd like to use MySQL. The table below is something you'd find in my database (simplified).

+------+------+------+------+
| id   | name | first| last |
+------+------+------+------+
|    1 | John | 1020 | 0814 |
|    2 | Ram  | 0827 | 0420 |
|    3 | Jack | 0506 | 0120 |
|    4 | Jill | 0405 | 0220 |
|    5 | Zara | 1201 | 1219 |
+------+------+------+------+

首先，条目必须是随机的，而不是id 4，我只需要1个条目。我做了这个工作： SELECT * FROM test WHERE id<> 4 ORDER BY rand（）LIMIT 1 。

First of all the entry has to be random, not id 4 and I only want 1 entry. I worked that out: SELECT * FROM test WHERE id <> 4 ORDER BY rand() LIMIT 1.

在这个表中，列的第一个和最后一个是格式为mmdd的日期整数）。所以约翰在今年的大部分时间都可以使用从10月20日至8月14日。另一方面，Zara只能在一段时间内使用; 12月1日至12月19日。

In this table the columns 'first' and 'last' are dates formatted as mmdd (both integers). So John is available for most of the year; from October 20th to August 14th. Zara on the other hand is only available for a small period of time; December 1st till December 19th.

我的问题：如何更改我的查询才能选择可用的人？在约翰的情况下，我不能使用之间，因为在1020和0814之间没有什么。

My question: how do I change my query to only select the available people? I can't use 'between' since, in John's case, there's nothing between 1020 and 0814.

我根本无法想像出来，还有其他人有一个类似的问题...有没有人有解决方案？

I just can't figure it out, there's must be other people that have a similar problem... Does anyone have a solution?

亲切问候

推荐答案

您需要区分两种情况。

1. 当最后，日期在同一年。然后，您可以使用之间的来匹配日期。

1. When first < last, the dates are in the same year. You can then use between to match dates.

当 first>最后一个，这意味着 last 在下一年。在这种情况下，匹配的日期是 date> = first OR date< = last 。

When first > last, it means last is in the next year. In this case, the dates that match are date >= first OR date <= last.

所以你的WHERE子句应该是：

So your WHERE clause should be:

WHERE IF(first < last, @date BETWEEN first AND last,
                       @date >= first OR date <= last)

这篇关于其他日期之间的日期，不考虑年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！