MySQL Mysql查询使用两个日期之间检索记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18101646/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql query to retrieve record using between for two dates
提问by user2660409
I have one requirement and i am totally confused how to write the query. I am a fresher in this company so please help me
我有一个要求，我完全困惑如何编写查询。我是这家公司的应届生，请帮帮我
User will select the following Dates:
用户将选择以下日期：
Ex:
fromDate: 2013-08-02toDate: 2013-09-03(YYYY-MM-DD)
例如：从日期：到日期2013-08-02：2013-09-03（YYYY-MM-DD）
id resort_id room_id Date1 Date2 price
5 35 45 2013-11-01 2013-11-30 3000.00
6 35 50 2013-07-25 2013-08-25 2000.00
7 35 541 2013-07-25 2013-08-25 4000.00
8 35 541 2013-08-26 2013-09-26 4000.00
Now i should get price based result for each date or sum of the price for both the date group by room id
现在我应该得到每个日期的基于价格的结果或按房间 id 的日期组的价格总和
Expected result
预期结果
id resort_id room_id Date1 Date2 price
6 35 50 2013-07-25 2013-08-25 2000.00
7 35 541 2013-07-25 2013-08-25 4000.00
8 35 541 2013-08-26 2013-09-26 4000.00
OR
或者
id resort_id room_id price
6 35 50 2000.00
7 35 541 8000.00
采纳答案by SoWhat
Try this query
试试这个查询
SELECT * FROM `rooms` WHERE "2013-08-02" between Date1 AND Date2 UNION
SELECT * FROM `rooms` WHERE "2013-09-03" BETWEEN Date1 AND Date2
Its a single query. Spread it on two lines for easier readability. I am pretty sure this query is missing something. I'll need a larger test set to verify. This won't select the middle cases. Change the first date with your user's selected dates.
它是一个单一的查询。将其分成两行，以便于阅读。我很确定这个查询遗漏了一些东西。我需要一个更大的测试集来验证。这不会选择中间情况。使用用户选择的日期更改第一个日期。
回答by Raja Ram T
SELECT * FROM lr_price_peak_rates WHERE `from_date`>='2014-04-08' AND `to_date`<='2014-04-30'
try the above query it can be working fine
尝试上面的查询它可以正常工作
回答by aizaz
Try this query
试试这个查询
SELECT
id,
resort_id,
room_id,
Date1,
Date2,
SUM(price) AS Total_Price
FROM
MyTable
WHERE Date1> '2013-02-08' AND Date2 < '2013-02-09'
回答by zxc
hey try this (sql server )
嘿试试这个（sql server）
SELECT room_id,SUM(price) FROM @table WHERE date1>='07/25/2013' AND date2<='09/26/2013' GROUP BY room_id
SELECT room_id,SUM(price) FROM @table WHERE date1>='07/25/2013' AND date2<='09/26/2013' GROUP BY room_id
回答by Raj Jagani
select * from table_name where date1 > '08/02/2013' AND date2 < '09/02/2013' ORDER BY date1 ASC, price ASC
回答by user3057670
SELECT * FROM roomsWHERE date between Date1 AND Date2 UNION
SELECT * FROM roomsWHERE date BETWEEN Date1 AND Date2
SELECT * FROM roomsWHERE 日期介于 Date1 和 Date2 之间 UNION SELECT * FROM roomsWHERE 日期 BETWEEN Date1 AND Date2
