blocks|key|461622|text|你只需要复制下面的查询并在mysql中执行，然后你就可以看到这个查询返回了从开始日期到结束日期的计数条目的数量。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|461623|你可以通过两种方式实现这一点，|461624|选项1|offset|length|style|BOLD|461625|++++SET+@FromDate+:=+'2016-04-1'++
++++SET+@ToDate+:=+'2016-04-30'+

++++SELECT+COUNT(*+)+FROM+clientbike
++++WHERE+stime+>=+@FromDate+AND+etime+<=+@ToDate|code-block|syntax|javascript|461626|这涵盖了所有日期范围的场景，如所选的@FromDate和@ToDate都在表的开始和结束列中。|CODE|461627|选项2|461628|+++++SET+@FromDate+:=+'2016-04-1'++
+++++SET+@ToDate+:=+'2016-04-30'+
+++++SELECT+count(*)+FROM+clientbike+WHERE+stime+BETWEEN+@FromDate+AND+@ToDate+AND+etime+BETWEEN+@FromDate+AND+@ToDate|461629|entityMap^0|0|0|0|3|0|0|I|9|S|7|0|0|3|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Z|8|@$F|10|G|11|H|I]]|9|@]|A|$]]|$1|J|3|K|5|L|7|12|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|13|8|@$F|14|G|15|H|Q]|$F|16|G|17|H|Q]]|9|@]|A|$]]|$1|R|3|S|5|6|7|18|8|@$F|19|G|1A|H|I]]|9|@]|A|$]]|$1|T|3|U|5|L|7|1B|8|@]|9|@]|A|$M|N]]|$1|V|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|W|$]]

You need to just copy below query and execute in mysql, then you can see this query is returning number of count entry from staring date to end date.

You can achieve this in two way,
 Option 1

<pre><code> SET @FromDate := '2016-04-1' 
 SET @ToDate := '2016-04-30' 

 SELECT COUNT(* ) FROM clientbike
 WHERE stime &gt;= @FromDate AND etime &lt;= @ToDate
</code></pre>

This is covering all date range scenario like selected <code>@FromDate</code> and <code>@ToDate</code> both are within start and end column in table.

Option 2

<pre><code> SET @FromDate := '2016-04-1' 
 SET @ToDate := '2016-04-30' 
 SELECT count(*) FROM clientbike WHERE stime BETWEEN @FromDate AND @ToDate AND etime BETWEEN @FromDate AND @ToDate
</code></pre>

blocks|key|461668|text|如果我理解正确的话，您想要的是重叠天数，其中EndDate是独占的。如果这是正确的，那么从这个开始：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|461669|SELECT+*
FROM+clientbike
WHERE+stime+<=+@ToDate
++++AND+etime+>=+@FromDate;|code-block|syntax|javascript|461670|此查询将返回clientbike中与搜索日期重叠的任何记录。以这种方式构造WHERE子句将检查sdate和edate之间是否存在start+this、end+this、@ToDate和@FromDate，以及sdate和edate是否位于@ToDate和@FromDate之间。|461671|接下来，我们需要修改SELECT|461672|SELECT+GREATEST(stime,+@FromDate)+AS+startDate,
++++LEAST(etime,+@ToDate)+AS+endDate
FROM+clientbike
WHERE+stime+<=+@ToDate
++++AND+etime+>=+@FromDate;|461673|GREATEST和LEAST的要点是确保我们只查看实际重叠的日期。例如:如果是stime+=+'2016-04-15'和@FromDate+=+'2016-04-22'，则为GREATEST(stime,+@FromDate)+=+'2016-04-22'。然后，我们将使用这两个字段来计算startDate和endDate之间的日期差异|461674|SELECT+GREATEST(stime,+@FromDate)+AS+startDate,
++++LEAST(etime,+@ToDate)+AS+endDate,+
++++DATEDIFF(LEAST(etime,+@ToDate),+GREATEST(stime,+@FromDate))+as+overlap
FROM+clientbike
WHERE+stime+<=+@ToDate
++++AND+etime+>=+@FromDate;|461675|SELECT中唯一重要的部分是DATEDIFF()函数，因此可以删除前面的两个字段。我只是把它们留在里面，这样你就可以看到发生了什么。|461676|entityMap^0|M|7|0|0|6|A|11|5|1B|5|1H|5|2C|7|2K|9|2W|5|32|5|3B|7|3J|9|0|A|6|0|0|0|8|9|5|13|K|1O|O|2F|15|41|9|4B|7|0|0|0|6|F|A|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]|$9|17|A|18|B|C]|$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]|$9|1J|A|1K|B|C]|$9|1L|A|1M|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|1N|8|@$9|1O|A|1P|B|C]]|D|@]|E|$]]|$1|O|3|P|5|H|7|1Q|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|1R|8|@$9|1S|A|1T|B|C]|$9|1U|A|1V|B|C]|$9|1W|A|1X|B|C]|$9|1Y|A|1Z|B|C]|$9|20|A|21|B|C]|$9|22|A|23|B|C]|$9|24|A|25|B|C]]|D|@]|E|$]]|$1|S|3|T|5|H|7|26|8|@]|D|@]|E|$I|J]]|$1|U|3|V|5|6|7|27|8|@$9|28|A|29|B|C]|$9|2A|A|2B|B|C]]|D|@]|E|$]]|$1|W|3|-4|5|6|7|2C|8|@]|D|@]|E|$]]]|X|$]]

If I understand correctly, you are wanting the number of overlapping days, with the <code>EndDate</code> being exclusive. If that is correct then start with this:

<pre><code>SELECT *
FROM clientbike
WHERE stime &lt;= @ToDate
 AND etime &gt;= @FromDate;
</code></pre>

This query will return any records from <code>clientbike</code> that overlap the searched dates. Structuring the <code>WHERE</code> clause this way checks for start overhang, end overhang, <code>@ToDate</code> and <code>@FromDate</code> between <code>sdate</code> and <code>edate</code>, and <code>sdate</code> and <code>edate</code> being between <code>@ToDate</code> and <code>@FromDate</code>.

Next we need to modify the <code>SELECT</code>:

<pre><code>SELECT GREATEST(stime, @FromDate) AS startDate,
 LEAST(etime, @ToDate) AS endDate
FROM clientbike
WHERE stime &lt;= @ToDate
 AND etime &gt;= @FromDate;
</code></pre>

The point of <code>GREATEST</code> and <code>LEAST</code> is to ensure that we only look at the dates that actually overlap. EX: if <code>stime = '2016-04-15'</code> and <code>@FromDate = '2016-04-22'</code>, then <code>GREATEST(stime, @FromDate) = '2016-04-22'</code>. We will then use these two fields to calculate the date difference between <code>startDate</code> and <code>endDate</code>:

<pre><code>SELECT GREATEST(stime, @FromDate) AS startDate,
 LEAST(etime, @ToDate) AS endDate, 
 DATEDIFF(LEAST(etime, @ToDate), GREATEST(stime, @FromDate)) as overlap
FROM clientbike
WHERE stime &lt;= @ToDate
 AND etime &gt;= @FromDate;
</code></pre>

The only part of the <code>SELECT</code> that matters is the <code>DATEDIFF()</code> function, so the preceding two fields can be removed. I just left them in so you can see what is going on.

blocks|key|461684|text|考虑到规范中的一种情况，它相当简单。规范中缺少的是stime和/或etime值相等的示例，或者完全或部分超出客户端自行车范围的示例。或等于stime的eTime值。用于搜索参数和列值。(我强烈怀疑规范中有更多的内容，而不仅仅是这一种情况。)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|461685|作为起点，这里有一个例子，它恰好适用于规范中的情况。这将为clientbike中的每一行返回一行。|461686|设置|461687|CREATE+TABLE+clientbike
(+id++++++BIGINT+NOT+NULL+AUTO_INCREMENT+PRIMARY+KEY
,+stime+++DATE+NOT+NULL+
,+etime+++DATE+NOT+NULL
);+

INSERT+INTO+clientbike+(stime,etime)+VALUES+
+('2016-04-01','2016-04-30')
,+('2016-05-01','2016-05-30')+;|code-block|syntax|javascript|461688|示例查询|461689|SELECT+b.id
+++++,+IF(q.etime+>=+b.stime+AND+q.stime+<+b.etime,+DATEDIFF(q.etime,q.stime),+0)+AS+days
++FROM+clientbike+b
+CROSS
++JOIN+(+SELECT+'2016-04-05'+%2B+INTERVAL+0+DAY+AS+stime
++++++++++++++,+'2016-04-07'+%2B+INTERVAL+0+DAY+AS+etime
+++++++)+q
+ORDER+BY+b.id++|461690|注意："search“值是在内联视图q中提供的。|461691|返回|461692|++id++days
----++----
+++1+++++2
+++2+++++0|461693|461694|作为另一个查询的可笑示例，该查询也将满足给定的规范，但实际上不可能扩展到其他情况...|461695|SELECT+b.id
+++++,+IF(++++b.stime+=+'2016-04-01'
++++++++++AND+b.etime+=+'2016-04-30'
++++++++++AND+q.stime+=+'2016-04-05'
++++++++++AND+q.etime+=+'2016-04-07'
+++++++,+'2+Days...'
+++++++,+''
+++++++)+AS+result
++++FROM+clientbike+b+++++
+CROSS
++JOIN+(+SELECT+'2016-04-05'+%2B+INTERVAL+0+DAY+AS+stime
++++++++++++++,+'2016-04-07'+%2B+INTERVAL+0+DAY+AS+etime
+++++++)+q|461696|461697|++id++result++
----++---------
+++1++2+Days...++
+++2++|461698|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|17|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|18|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|19|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@]|A|$]]|$1|S|3|T|5|H|7|1C|8|@]|9|@]|A|$I|J]]|$1|U|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1E|8|@]|9|@]|A|$]]|$1|X|3|Y|5|H|7|1F|8|@]|9|@]|A|$I|J]]|$1|Z|3|R|5|6|7|1G|8|@]|9|@]|A|$]]|$1|10|3|11|5|H|7|1H|8|@]|9|@]|A|$I|J]]|$1|12|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|13|$]]

Given the one case in the specification, it's fairly straightforward. What's missing from the specification is examples of stime and/or etime values that are equal, or are entirely or partially outside the clientbike range. Or etime values that are equal to stime. For both search parameters and for column values. (I strongly suspect there is more to the specification than just the one case.)

As a starting point, here's an example that happens to "work" for the case in the specification. This returns a row for every row in clientbike.

setup

<pre><code>CREATE TABLE clientbike
( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
, stime DATE NOT NULL 
, etime DATE NOT NULL
); 

INSERT INTO clientbike (stime,etime) VALUES 
 ('2016-04-01','2016-04-30')
, ('2016-05-01','2016-05-30') ;
</code></pre>

example query

<pre><code>SELECT b.id
 , IF(q.etime &gt;= b.stime AND q.stime &lt; b.etime, DATEDIFF(q.etime,q.stime), 0) AS days
 FROM clientbike b
 CROSS
 JOIN ( SELECT '2016-04-05' + INTERVAL 0 DAY AS stime
 , '2016-04-07' + INTERVAL 0 DAY AS etime
 ) q
 ORDER BY b.id 
</code></pre>

NOTE: The "search" values are supplied in the inline view q.

returns 

<pre><code> id days
---- ----
 1 2
 2 0
</code></pre>

<hr>

As a ridiculous example of another query that would also satisfy the given specification but would be virtually impossible to extend to other cases... 

<pre><code>SELECT b.id
 , IF( b.stime = '2016-04-01'
 AND b.etime = '2016-04-30'
 AND q.stime = '2016-04-05'
 AND q.etime = '2016-04-07'
 , '2 Days...'
 , ''
 ) AS result
 FROM clientbike b 
 CROSS
 JOIN ( SELECT '2016-04-05' + INTERVAL 0 DAY AS stime
 , '2016-04-07' + INTERVAL 0 DAY AS etime
 ) q
</code></pre>

returns

<pre><code> id result 
---- ---------
 1 2 Days... 
 2 
</code></pre>

I have a table which is clientbike 

SELECT stime,etime FROM clientbike

<pre><code> stime | etime 
 --------------------------
 2016-04-01 | 2016-04-30
</code></pre>

if anybody search stime = 2016-04-05 &amp; etime = 2016-04-07

result Found 

<pre><code> 2 Days... 
</code></pre>

Can someone explain how to do using sql / mysql ?

Thnaks

search between two dates using MYSQL / SQL?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个是clientbike的桌子从客户端自行车中选择stime、etime    stime       |  etime     --------------------------    2016-04-01  |  2016-04-30如果有人搜索stime = 2016-04-05 & etime = 20...

问使用MYSQL / SQL在两个日期之间进行搜索？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用MYSQL / SQL在两个日期之间进行搜索？EN