在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.start
 
Which 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}"
end
 
 UPDATE  
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}"
end
 
UPDATE 
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.messages
 
All of this is good enough data, i think.  
Good luck.

我如何在我的Rails应用程序中测试ActiveRecord :: RecordNotFound？(How can I test ActiveRecord::RecordNotFound in my rails app?)

有两件事你可以做。首先是让ActionController在解救ActiveRecord :: RecordNotFound时提供默认操作： class PostsControllerTest < ActionController::TestCase test "raises RecordNotFound when not found" do assert_raises(ActiveRecord::RecordNotFound) do get :show, :id => ...

在Rails中从ActiveRecord :: RecordNotFound救援(rescue from ActiveRecord::RecordNotFound in Rails)

如果你想使用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 w...

ActiveRecord RecordNotFound错误(ActiveRecord RecordNotFound Error)

你的自定义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 rout...

Rails如何在开发模式下自动从ActiveRecord :: RecordNotFound中解救？(How can Rails automatically rescue from ActiveRecord::RecordNotFound in development mode?)

也许你想关掉这个？： 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/developme...

BooksController＃update中的ActiveRecord :: RecordNotFound(ActiveRecord::RecordNotFound in BooksController#update)

更换 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 = Sub...

ActiveRecord RecordNotFound本地化(ActiveRecord RecordNotFound localization)

我有理由告诉用户更多信息： 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}" ..什么...

从Rails中的ActiveRecord :: RecordNotFound获取额外信息(Get extra information from ActiveRecord::RecordNotFound in Rails)

比如说， 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, ...

Rails：“找不到'id'='的电影（ActiveRecord :: RecordNotFound）(Rails: “Couldn't find Movie with 'id'=” (ActiveRecord::RecordNotFound))

你需要这样做才能保存数据，在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 ...

Rails搜索查询LIKE - ActiveRecord :: RecordNotFound(Rails Search Query for LIKE - ActiveRecord::RecordNotFound)

这看起来很像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. Sup...

关系随机引发ActiveRecord :: RecordNotFound(Relation randomly raises ActiveRecord::RecordNotFound)

通过浏览评论列表可以找到我们得出结论的方式。我们的调查结果摘要如下：订单是缓存的，还是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...

Linux 上的free命令详解

Linux 上的free命令详解的内容摘要：解释一下Linux上free命令的输出。　　下面是free ...

mysql问题

利用PreparedStatement执行插入，更新的操作时候：如： PreparedState ...

Nike Free Run Shoes Sale Online Shopping

(Turner Lair), Jonah Bobo (Zach Lair), Joshua Lucas ...

关于netbeans和mysql的问题

为什么在netbeans里进行数据迁移总说缺少mysql.io这样一个类似的东西，应该怎么装mys ...

Hadoop 异常记录 ERROR: org.apache.hadoop.hbase.MasterNotRunningException: Retried 7 times

当我把Hadoop、hbase安装配置（具体参考这里）好了之后，启动hbase的shell交互模式，输 ...

关于mysql 的 sql

有两张表如下: a表: id name A a B b b表: cid cname 1 ...

Working on Free Software

This was written in December, 1999 Lots of programm ...

Mysql删除binlog(安全清理)

如果你的 Mysql 搭建了主从同步 , 或者数据库开启了 log-bin 日志 , 那么随着时间的推 ...

MySQL用户与授权

本文主要内容是对mysql用户管理,包括创建mysql用户,对用户授权,设置与更改用户密码,撤销用户权 ...

shell操作mysql初步

shell操作mysql的两种方式，方式一：echo "${SQL}" | ${CONNECT_MYS ...

python的访问器方法有哪些

使用方法： class A(object): def foo(self,x): #类实例方法 print "executing foo(%s,%s)"%(self,x) @classmethod def class_foo(cls,x): #类方法 print "executing class_foo(%s,%s)"%(cls,x) @staticmethod def static_foo(x): #静态方法 print "executing static_foo(%s)"%x调用方法： a =

使用Zend Framework 2中的JOIN sql检索数据(Retrieve data using JOIN sql in Zend Framework 2)

我认为你必须将两个TableGetway传递给UserTable构造。你必须改变Module.php看看： public function getServiceConfig() { return array( 'factories' => array( 'User\Model\UserTable' => function($sm) { $userTableGateway = $sm->get('UserTable

透明度错误IE11(Transparency bug IE11)

这是一个渲染错误，由使用透明度触发，使用bootstrap用于在聚焦控件周围放置蓝色光环的box-shadow属性。可以通过添加以下类覆盖来解决它。 .form-control:hover { -webkit-box-shadow: 0px 0px 5px 0px rgba(0,0,255,1); -moz-box-shadow: 0px 0px 5px 0px rgba(0,0,255,1); box-shadow: 0px 0px 4px 0px rgba(0,0,255,1)

linux的基本操作命令。。。

ls 显示目录 mkdir 建立目录 cd 进入目录

响应navi重叠h1和nav上的h1链接不起作用(Responsive navi overlaps h1 and navi links on h1 isn't working)

将z-index设置为.main-nav这将解决您的重叠问题 .main-nav { position:relative; z-index:9; } set z-index to .main-nav This will fix your overlaping issue .main-nav { position:relative; z-index:9; }

在C中读取文件：“r”和“a +”标志的不同行为(Reading a File in C: different behavior for “r” and “a+” flags)

这是因为模式规范"a"打开一个文件以便追加，文件指针在末尾。如果您尝试从此处读取，则由于文件指针位于EOF，因此没有数据。您应该打开"r+"进行阅读和写作。如果在写入之前读取整个文件，则在写入更多数据时，文件指针将正确定位以追加。如果这还不够，请探索ftell()和fseek()函数。 That is because the mode spec "a" opens a file for appending, with the file pointer at the end. If you

NFC提供什么样的带宽？(What Kind of Bandwidth does NFC Provide?)

支持空中接口的数据速率是一回事。在消除协议开销，等待eeprom写入以及所有需要时间的其他内容之后，您看到的数据速率是完全不同的故事。长话短说，从标签读取或进行对等传输时的实际数据速率峰值约为2.5千字节/秒。取决于具体的标签或对等技术，它可能比这慢很多。 The supported data-rates of the air-interface are one thing. The data-rate that you see after removing protocol overhe

元素上的盒子阴影行为(box-shadow behaviour on elements)

它看起来像只在Windows上的Chrome的错误。我在Google Canary （Chrome 63）中也进行了测试，问题依然存在，所以有可能它不会很快修复。这个问题是由overflow: auto引起的overflow: auto ，在你的情况下，它可以很容易地通过删除或设置为可见（默认）来解决。但是，将鼠标悬停在右侧（顶部和底部）时，会出现滚动条。一个解决方案可以设置overflow: hidden的身体，所以预期的结果是所需的。我想指出，这不是一个很好的解决方案，但我建议暂

Laravel检查是否存在记录(Laravel Checking If a Record Exists)

这取决于您是否要以后与用户合作，或仅检查是否存在。如果要使用用户对象（如果存在）： $user = User::where('email', '=', Input::get('email'))->first(); if ($user === null) { // user doesn't exist } 如果你只想检查 if (User::where('email', '=', Input::get('email'))->count() > 0) { // user found

设置base64图像的大小javascript - angularjs(set size of a base64 image javascript - angularjs)

$scope.getData= function () { var reader = new FileReader(); reader.onload = $('input[type=file]')[0].files; var img = new Image(); img.src =(reader.onload[0].result); img.onload = function() { if(this.width > 640