我有一張桌子，上面有交易日期和這樣的柜臺
| 沒有卡 | 日期 | 柜臺 |
|---|---|---|
| 1377370849 | 2022/11/13 11:14:12 | 76 |
| 1377370849 | 2022/11/13 11:14:33 | 77 |
| 1377370849 | 2022/11/13 11:14:45 | 80 |
| 1377370849 | 2022/11/13 11:14:55 | 85 |
| 1377370849 | 2022/11/13 11:24:05 | 86 |
| 1377370849 | 2022/11/13 11:24:11 | 87 |
| 1377370849 | 2022/11/13 11:24:12 | 88 |
| 1377370849 | 2022/11/13 11:24:13 | 12 |
| 1377370849 | 2022/11/13 11:34:10 | 89 |
| 1377370849 | 2022/11/13 11:44:01 | 90 |
| 1377370849 | 2022/11/13 11:44:05 | 91 |
| 1377370849 | 2022/11/13 11:44:11 | 92 |
| 1377370849 | 2022/11/13 11:54:22 | 120 |
| 1377370849 | 2022/11/13 11:54:26 | 93 |
| 1377370849 | 2022/11/13 11:54:32 | 99 |
| 1377370849 | 2022/11/13 11:54:45 | 100 |
我必須找到計數器 12 和計數器 120。計數器是一個遞增計數器，它在 250 處再次變為 1。12
=> 計數器遞增但下降到 12 并在 12
120 => 計數器跳轉到 120 并變為 93后再次變為 89 120后
我如何為此寫入 SQL 查詢。（甲骨文 11）
uj5u.com熱心網友回復：
你可以使用LEADand LAG。如果我理解正確，您的計數器在上一個和下一個計數器之間時有效。可以這樣驗證：
WITH orderedData AS
(SELECT no_card, yourdate, counter,
LAG(counter, 1, 0) OVER (ORDER BY yourdate) AS counter_prev,
LEAD(counter, 1, 0) OVER (ORDER BY yourdate) AS counter_next
FROM yourtable)
SELECT counter FROM orderedData
WHERE NOT counter BETWEEN counter_prev AND counter_next
AND counter_prev < counter_next;
這將為您的樣本資料回傳 12 和 120，因為這兩個值不滿足此條件。
注意：如果計數器按卡號和/或日期分開，則可能需要在and部分添加PARTITION BYcard_no 和/或 date 子句。從您的示例資料中不清楚。LAGLEAD
因此，如有必要，請隨時擴展此查詢。
uj5u.com熱心網友回復：
行
WHERE counter NOT BETWEEN
CASE WHEN 250 = LAG(counter) OVER(PARTITION BY no_card ORDER BY dat) THEN 0
ELSE
LAG(counter) OVER(PARTITION BY no_card ORDER BY dat) END
AND LEAD(counter) OVER(PARTITION BY no_card ORDER BY dat)
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/532679.html
標籤：sql甲骨文
上一篇：熊貓列中按升序排序的問題