問題描述

試圖找出適用于多種情況的查詢.簡而言之，數(shù)據(jù)可能處于兩種情況之一.假設(shè)我正在尋找在給定時(shí)間范圍內(nèi)發(fā)生的事件的 record_id:6/26/2012 10:00AM 和 6/27/2012 11:00AM 數(shù)據(jù)庫中的記錄可能如下所示:

Trying to figure out a query that could work for multiple situations. In a nutshell the data can be in one of twosituations. Lets say I'm looking for the record_id for events happened during a given time frame: 6/26/2012 10:00AM and 6/27/2012 11:00AM The records can look like this in the database:

Record    Event     Time
1         Start     6/26/2012 10:05AM
1         End       6/26/2012 10:45AM
2         Start     6/26/2012 09:55AM
2         End       6/26/2012 11:05AM

獲取記錄 1 很容易，只需使用 between 函數(shù)，但我在試圖找出一個(gè)查詢以返回記錄 1 和 2 時(shí)遇到困難.

Getting record 1 is easy, just using the between function, but I'm stumbling trying to figure out a query to return both records 1 and 2.

建議?

推薦答案

您可以簡單地?cái)U(kuò)大范圍，但這可能會(huì)帶回與您預(yù)期不同的記錄.

You can simply widen the range, but this may bring back different records than you intend.

SELECT  RECORD, EVENT, TIME 
FROM SO_RecordEvent AS R
WHERE TIme BETWEEN  '6/26/2012 9:55AM'  AND '6/26/2012 11:06AM'

這將返回具有該范圍內(nèi)開始或結(jié)束時(shí)間的任何記錄的所有記錄，包括落在該范圍之外的相關(guān)記錄(換句話說，只有一次在該范圍內(nèi)的記錄 - 但在此之前或結(jié)束之前開始)after - 它仍然會(huì)顯示它之外的開始或結(jié)束時(shí)間)并允許您縮短范圍.

This will return all records for anything that has either a start or end time within the range, including the associated records falling outside of the range (In other words records that only have one time in the range - but began before or ended after - it will still show the start or end time outside of it) and allow you you shorten your range.

 ;WITH X AS 
 (
    SELECT RECORD,EVENT,TIME FROM SO_RecordEvent 
 )
 SELECT R.RECORD,R.EVENT,R.TIME 
 FROM SO_RecordEvent AS R
 INNER JOIN X ON R.Record = X.Record
 WHERE X.TIme BETWEEN  '6/26/2012 10:05AM'  AND '6/26/2012 11:05AM'
 GROUP BY R.RECORD,R.EVENT,R.TIME

但我認(rèn)為您可能真的想要這樣的東西，它確實(shí)為您提供了在那段時(shí)間開始的所有內(nèi)容，即使它在范圍之外開始和結(jié)束，就像您的記錄 2 示例一樣.

But I think you may really want something like this, which truly gives you everything that was started during that time, even if it started AND ended outside of the range, as is your record 2 example.

改變邏輯 - 決定以這種方式思考它而不是解決情況 - 任何在開始和結(jié)束內(nèi)開始的事情，在開始和結(jié)束內(nèi)結(jié)束的任何事情，以及在開始和結(jié)束之后開始的任何事情.我認(rèn)為這涵蓋了在此期間運(yùn)行的任何內(nèi)容(開始之前結(jié)束之內(nèi)，開始和結(jié)束之內(nèi)，開始之內(nèi)和結(jié)束之后，以及開始之前和之后結(jié)束)

Changed logic - instead of addressing situations decided to think about it this way - anything that started within start and end, anything that ended within start and end, and anything that started before and ended after. I think this covers anything that runs during this time (starts before ends within, starts and ends within, starts within and ends after, and starts before and ends after)

  SELECT X.RECORD,X.TIME AS STARTTIME,Y.TIME AS ENDTIME 
  FROM SO_RecordEvent AS X
  INNER JOIN SO_RecordEvent Y ON Y.Record = X.Record AND Y.EVENT = 'END'
  WHERE  X.EVENT = 'START'
  AND 
  ((X.TIME >= '6/26/2012 10:00AM' AND X.TIME <= '6/26/2012 11:00AM')
  OR (Y.TIME >= '6/26/2012 10:00AM' AND Y.TIME <= '6/26/2012 11:00AM')
  OR (X.TIME <= '6/26/2012 10:00AM' AND Y.TIME >= '6/26/2012 11:00AM'))

要使用的變量:

 DECLARE @START datetime, @END datetime
 SET @START = '6/26/2012 10:00AM'
 SET @END = '6/26/2012 11:00AM'

   SELECT X.RECORD,X.TIME AS STARTTIME,Y.TIME AS ENDTIME 
   FROM SO_RecordEvent AS X
   INNER JOIN SO_RecordEvent Y ON Y.Record = X.Record AND Y.EVENT = 'END'
   WHERE  X.EVENT = 'START'
   AND 
  ((X.TIME >= @START AND X.TIME <= @END)
  OR (Y.TIME >= @START AND Y.TIME <= @END)
  OR (X.TIME <= @START AND Y.TIME >= @END))

這篇關(guān)于在時(shí)間范圍內(nèi)查找事件的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！