本文介紹了從相隔小于指定分鐘數(shù)的行中選擇非重復(fù)值的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
我有一張大表，格式如下:
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
如果第二列中沒有重復(fù)數(shù)字的行相隔不到 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 的第二行是重復(fù)的，因為它與前一行的間隔小于 15 分鐘，不應(yīng)被選中.
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.
最后一行與第一行的編號相同，但它不是重復(fù)的，因為它發(fā)生在超過 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
這篇關(guān)于從相隔小于指定分鐘數(shù)的行中選擇非重復(fù)值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請聯(lián)系我們刪除處理，感謝您的支持！