如何比较一行中的重叠值? [英] How do I compare overlapping values within a row?

问题描述

此SQL查询似乎有问题:

I seem to have a problem with this SQL query:

SELECT * FROM appts 
WHERE timeStart >='$timeStart' 
AND timeEnd <='$timeEnd' 
AND dayappt='$boatdate'

时间格式为军事时间.物流是可以在早上7点至下午1点或晚上9点至下午1点或晚上9点至下午5点预订船租.如果该范围内有一个appt，则应返回appts，但事实证明它不一致.如果我选择上午9点至下午1点，即使它与上午9点至下午1点重叠，它也会忽略从上午7点开始的应用.如果我选择9到5，即使应该是早上7点到下午1点，也不会返回任何内容.如何制作一个包含从timeStart到timeEnd的整个范围(包括那些重叠部分)的SQL语句?

The time is formatted as military time. The logistics is that a boat rental can be reserved at 7am til 1pm or 9am til 1pm or 9am til 5pm. If there is an appt within that range, it should return appts but it has proven to be inconsistent. If I pick 9am til 1pm, it will ignore appts that started with 7am even though it overlaps 9am-1pm. If I pick 9 to 5, it will return nothing even though it should with the 7am to 1pm. How do I make a SQL statement that includes the whole range from timeStart to timeEnd including those that overlap?

推荐答案

正确的检查应如下所示:

The correct check would look like this:

SELECT * FROM appts 
WHERE timeStart <='$timeEnd' 
AND timeEnd >='$timeStart' 
AND dayappt='$boatdate'

还给出了其他很好的解释，但我将继续进行介绍，并用我自己如何可视化的另一种解释进行更新.考虑到两个时间段，大多数人都在寻找每个可能的重叠，他们试图考虑可以使约会重叠的开始和结束的每种组合.我认为这是因为两个时间段何时不重叠，出于某种原因，这对我来说比较容易.

Other good explanations have been given but I'll go ahead and update it with an alternative explanation of how I visualize this myself. Most people are looking for each possible overlap, considering two time periods, they are trying to think of each combination of start and end that can make an appointment overlap. I think about it as when do two time periods not overlap which for some reason is easier for me.

说我要检查的时间段是今天，我想找到今天不重叠的任何时间段.确实只有两种情况，时间段从今天开始(PeriodStart> EndOfToday)或时间段从今天开始(PeriodEnd< StartOfToday).

Say the time period I am checking for is today, I want to find any time period that does not overlap today. There are really only two scenarios for that, either the time period starts after today (PeriodStart > EndOfToday) or the time period ends before today (PeriodEnd < StartOfToday).

鉴于我们有一个不重叠的简单测试:
(PeriodStart> EndOfToday)或(PeriodEnd< StartOfToday)

Given that we havea simple test for not overlapping:
(PeriodStart > EndOfToday) OR (PeriodEnd < StartOfToday)

快速浏览一下，您可以对重叠进行简单的测试:
(PeriodStart< = EndOfToday)和(PeriodEnd> = StartOfToday)

A quick flip around and you have a simple test for overlap:
(PeriodStart <= EndOfToday) AND (PeriodEnd >= StartOfToday)

-Shane

这篇关于如何比较一行中的重叠值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！