本文介紹了從相隔小于指定分鐘數的行中選擇非重復值的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
我有一張大表，格式如下:
I have a huge table with the following format:
DATETIME NUMBER
--------------------------------------
2009-03-31 16:05:52.000 2453651622
2009-03-31 16:16:12.000 30206080
2009-03-31 16:16:16.000 16890039
2009-03-31 16:16:28.000 2452039696
2009-03-31 16:16:33.000 140851934
2009-03-31 16:16:51.000 2453120306
2009-03-31 16:16:57.000 2453120306
...
2009-04-01 21:15:24.000 2453651622
如果第二列中沒有重復數字的行相隔不到 15 分鐘，我該如何選擇它們?
How can I select the rows that don't have duplicate numbers in the second column if they occur less than 15 minutes apart?
在前面的示例中，編號為 2453120306 的第二行是重復的，因為它與前一行的間隔小于 15 分鐘，不應被選中.
In the previous example, the second row with number 2453120306 is a duplicate because it is less than 15 minutes apart from the previous one, and should not be selected.
最后一行與第一行的編號相同，但它不是重復的，因為它發生在超過 24 小時之后.
The last row has the same number as the first row, but it is not a duplicate because it occurs more that 24 hours later.
推薦答案
-- distinct required in case there are rows with
-- exactly the same values for datetime and number
SELECT DISTINCT a.*
FROM your_table AS a
LEFT JOIN your_table AS b
ON a.[number] = b.[number]
AND a.[datetime] > b.[datetime]
AND a.[datetime] <= DATEADD(minute, 15, b.[datetime])
WHERE b.Number IS NULL
這篇關于從相隔小于指定分鐘數的行中選擇非重復值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！