dtll2016
2011-09-16 08:48
浏览 38
已采纳

如果在输入的日期范围内不存在,请获取最近的日期

Im doing a backend now, and I'm trying to make a list of number of rooms by date range.

DB Structure

id room_type_id room_count date_applied
1  1            2          2011-09-01
1  1            3          2011-09-05
1  1            1          2011-09-06

In the HTML:

If the user inputted From: 2011-Sept-01 To: 2011-Sept-06. This would be the display. (This is already OK)

Date          Room Count
2011-Sept-1   2
2011-Sept-2   2
2011-Sept-3   2
2011-Sept-4   2
2011-Sept-5   3
2011-Sept-6   1

And my problem now is that, when the user inputted into From and To is already out of range from the data stored in the database. Sample: From: 2011-09-07 To: 2011-09-09

The Output should be displayed in the HTML should be look like this.

Date          Room Count
2011-Sept-7   1
2011-Sept-8   1
2011-Sept-9   1

It will get the closest/last data in the database. For this sample it would be 2011-09-06

Thanks for any help.

图片转代码服务由CSDN问答提供 功能建议

我正在做后端,我正在尝试按日期范围列出房间数量。< / p>

数据库结构

  id room_type_id room_count date_applied 
1 1 2 2011-09-01 
1 1 3  2011-09-05 
1 1 1 2011-09-06 
   
 
 

在HTML中:

如果用户输入了 来自: 2011-Sept-01 To: 2011-Sept-06 。 这将是显示器。 (这已经没问了)

 日期房间数量
2011-Sept-1 2 
2011-Sept-2 2 
2011-Sept-3 2 
2011-Sept-4  2 
2011-Sept-5 3 
2011-Sept-6 1 
   
 
 

我现在的问题是,当输入到From和To的用户已经输出时 范围来自存储在数据库中的数据。 示例:来自:2011-09-07收件人:2011-09-09

输出应显示在HTML中,应该如下所示。 < pre> Date Room Count 2011-Sept-7 1 2011-Sept-8 1 2011-Sept-9 1

它将获得 数据库中最近/最后的数据。 对于此示例,它将 2011-09-06

感谢您的帮助。

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douya2982 2011-09-16 08:56
    已采纳

    For that you may need to run a sub query finding the immediate lowest date? and then start from that date to the end date.

    Forexample.

    SELECT * FROM tbl WHERE date BETWEEN (SELECT date FROM tbl WHERE date < lower_range LIMIT 1 ) AND upper_range
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题