在MySQL中查找'免费'时间(Finding 'free' times in MySQL)
我有一张桌子如下：
mysql> DESCRIBE student_lectures; +------------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | course_module_id | int(11) | YES | MUL | NULL | | | day | int(11) | YES | | NULL | | | start | datetime | YES | | NULL | | | end | datetime | YES | | NULL | | | cancelled_at | datetime | YES | | NULL | | | lecture_type_id | int(11) | YES | | NULL | | | lecture_id | int(11) | YES | | NULL | | | student_id | int(11) | YES | | NULL | | | created_at | datetime | YES | | NULL | | | updated_at | datetime | YES | | NULL | | +------------------+----------+------+-----+---------+----------------+我基本上想找到一次演讲没有发生的时间 - 所以要做到这一点，我正在考虑将重叠讲座分组在一起（例如，上午9点 - 10点和上午10点 - 上午11点的演讲将显示为单班9 am-11am讲座）。 可能会有两个以上的讲座背靠背。
我目前有这样的：
SELECT l.start, l2.end FROM student_lectures l LEFT JOIN student_lectures l2 ON ( l2.start = l.end ) WHERE l.student_id = 1 AND l.start >= '2010-04-26 09:00:00' AND l.end <= '2010-04-30 19:00:00' AND l2.end IS NOT NULL AND l2.end != l.start GROUP BY l.start, l2.end ORDER BY l.start, l2.start其中返回：
+---------------------+---------------------+ | start | end | +---------------------+---------------------+ | 2010-04-26 09:00:00 | 2010-04-26 11:00:00 | | 2010-04-26 10:00:00 | 2010-04-26 12:00:00 | | 2010-04-26 10:00:00 | 2010-04-26 13:00:00 | | 2010-04-26 13:15:00 | 2010-04-26 16:15:00 | | 2010-04-26 14:15:00 | 2010-04-26 16:15:00 | | 2010-04-26 15:15:00 | 2010-04-26 17:15:00 | | 2010-04-26 16:15:00 | 2010-04-26 18:15:00 | ...etc...我从中找到的输出是：
+---------------------+---------------------+ | start | end | +---------------------+---------------------+ | 2010-04-26 09:00:00 | 2010-04-26 13:00:00 | | 2010-04-26 13:15:00 | 2010-04-26 18:15:00 |任何帮助表示感谢，谢谢！
I've got a table as follows:
mysql> DESCRIBE student_lectures; +------------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | course_module_id | int(11) | YES | MUL | NULL | | | day | int(11) | YES | | NULL | | | start | datetime | YES | | NULL | | | end | datetime | YES | | NULL | | | cancelled_at | datetime | YES | | NULL | | | lecture_type_id | int(11) | YES | | NULL | | | lecture_id | int(11) | YES | | NULL | | | student_id | int(11) | YES | | NULL | | | created_at | datetime | YES | | NULL | | | updated_at | datetime | YES | | NULL | | +------------------+----------+------+-----+---------+----------------+I'm essentially wanting to find times when a lecture doesn't happen - so to do this I'm thinking a query to group overlapping lectures together (so, for example, 9am-10am and 10am-11am lectures will be shown as a single 9am-11am lecture). There may be more than two lectures back-to-back.
I've currently got this:
SELECT l.start, l2.end FROM student_lectures l LEFT JOIN student_lectures l2 ON ( l2.start = l.end ) WHERE l.student_id = 1 AND l.start >= '2010-04-26 09:00:00' AND l.end <= '2010-04-30 19:00:00' AND l2.end IS NOT NULL AND l2.end != l.start GROUP BY l.start, l2.end ORDER BY l.start, l2.startWhich returns:
+---------------------+---------------------+ | start | end | +---------------------+---------------------+ | 2010-04-26 09:00:00 | 2010-04-26 11:00:00 | | 2010-04-26 10:00:00 | 2010-04-26 12:00:00 | | 2010-04-26 10:00:00 | 2010-04-26 13:00:00 | | 2010-04-26 13:15:00 | 2010-04-26 16:15:00 | | 2010-04-26 14:15:00 | 2010-04-26 16:15:00 | | 2010-04-26 15:15:00 | 2010-04-26 17:15:00 | | 2010-04-26 16:15:00 | 2010-04-26 18:15:00 | ...etc...The output I'm looking for from this would be:
+---------------------+---------------------+ | start | end | +---------------------+---------------------+ | 2010-04-26 09:00:00 | 2010-04-26 13:00:00 | | 2010-04-26 13:15:00 | 2010-04-26 18:15:00 |Any help appreciated, thanks!
原文：https://stackoverflow.com/questions/2798249
更新时间：2024-04-22 18:04
最满意答案
比如说，
begin @user = User.find(params[:id]) rescue ActiveRecord::RecordNotFound flash[:notice] = "#No such record in User for id :: {params[:id]} on #{action_name}" endUPDATE
flash[:notice] = t('flash.recordnotfound',:class_name => self.class.name, :column_name => params[:id], :action_name => action_name)现在在你的
config/locales/en.yml（这有助于翻译，请参考i18n）flash: recordnotfound: "Sorry, no record od %{column_name} in class %{class_name} was found on you action %{action_name}"如果您不想使用区域设置，只需将此信息放在
flash[:notice]。更有活力？
写一个函数并在那里使用相同的flash [：notice]。 不要伤害。
想要更多数据？
这是一个快速的解决方案，我总是在我的观点中
<%= params%>，以便轻松了解最新情况以及最新消息。 然后，您可以打开rails控制台并播放不同的操作等。user = User.new user.save user.errors.messages我认为所有这些都是足够好的数据。
祝你好运。
Say for example,
begin @user = User.find(params[:id]) rescue ActiveRecord::RecordNotFound flash[:notice] = "#No such record in User for id :: {params[:id]} on #{action_name}" endUPDATE
flash[:notice] = t('flash.recordnotfound',:class_name => self.class.name, :column_name => params[:id], :action_name => action_name)Now in your
config/locales/en.yml(this would help translate, refer to i18n here)flash: recordnotfound: "Sorry, no record od %{column_name} in class %{class_name} was found on you action %{action_name}"If you do not want to use locales just put up this information in
flash[:notice]itself.More dynamic ?
Write a function and use the same flash [:notice] there. Wont hurt at all.
want more data ?
Heres a quick solution, i always
<%= params%>in my views to know easily whats going and whats coming. You can then open your rails console and play along with different actions and so on.user = User.new user.save user.errors.messagesAll of this is good enough data, i think.
Good luck.
相关问答
更多-
有两件事你可以做。 首先是让ActionController在解救ActiveRecord :: RecordNotFound时提供默认操作： class PostsControllerTest < ActionController::TestCase test "raises RecordNotFound when not found" do assert_raises(ActiveRecord::RecordNotFound) do get :show, :id => 1234 ...
-
在Rails中从ActiveRecord :: RecordNotFound救援(rescue from ActiveRecord::RecordNotFound in Rails)[2023-11-22]
如果你想使用rescue语句，你需要以某种方式使用find() ，这会引发异常，也就是传递你想要查找的id。 def edit @post = Load.scoped_by_user_id(session[:user_id]).find(params[:id]) rescue ActiveRecord::RecordNotFound flash[:notice] = "Wrong post it" redirect_to :action => 'index' end If you want t ... -
你的自定义get 'memory_game/play应该在resources :memory_game之前。 Rails按routes.rb文件中列出的顺序评估路由，最接近文件顶部的路由接收最高优先级。 Your custom get 'memory_game/play should come before resources :memory_game. Rails evaluates routes in the order in which they are listed in the routes.rb ...
-
也许你想关掉这个？： config.consider_all_requests_local = false 默认情况下，这在config/environments/development.rb设置为true 。 Perhaps you want to turn this off?: config.consider_all_requests_local = false This is set to true in config/environments/development.rb ...
-
更换 def update #below is the line I am getting the error.... @book = Book.find(book1_params) if @book.update_attributes(params[:book]) redirect_to :action => 'show', :id => @book else @subjects = Subject. ...
-
我有理由告诉用户更多信息： exception.message.match /^Couldn't find (\w+) with (id=([\S]*))?/ msg = t 'activerecord.exceptions.not_found', klass: $1, id: $3 因为我想要一个捷克语翻译： cs: activerecord: exceptions: not_found: "Nelze nalézt %{klass} s id=%{id}" ..什么是硬编码的 ...
-
比如说， begin @user = User.find(params[:id]) rescue ActiveRecord::RecordNotFound flash[:notice] = "#No such record in User for id :: {params[:id]} on #{action_name}" end UPDATE flash[:notice] = t('flash.recordnotfound',:class_name => self.class.name, :colu ...
-
你需要这样做才能保存数据，在create动作中代替params[:movie_id] ，使用comment_params def create @movie = Movie.new(comment_params) if @movie.save redirect_to new_movie_path end end 希望这可以帮助！ You need to do this in order to save the data, in create action, in place of pa ...
-
Rails搜索查询LIKE - ActiveRecord :: RecordNotFound(Rails Search Query for LIKE - ActiveRecord::RecordNotFound)[2022-10-19]
这看起来很像Rails 2代码。 Support.find(:all, :conditions => ['name LIKE ?', "%#{params[:search]}%"]) 在Rails 4语法中，它应该如下所示： Support.where('name LIKE ?', "%#{params[:search]}%") 我建议使用Rails指南来进一步阅读ActiveRecord查询界面 。 This looks suspiciously like Rails 2 code. Support. ... -
关系随机引发ActiveRecord :: RecordNotFound(Relation randomly raises ActiveRecord::RecordNotFound)[2023-08-16]
通过浏览评论列表可以找到我们得出结论的方式。 我们的调查结果摘要如下： 订单是缓存的，还是current_user已过时（以及缓存的关联） 直接订购工程（即Order.find / User.find（current_user.id）.orders ... 我们到达的解决方案是： current_user.reload! 在表演之前 current_user.orders.find(params[:id]) The way we arrived at the conclusion can be foun ...