我使用以下腳本得到如下結果：
SELECT
id, (2022 - age) yearId, age, [value],
CASE
WHEN LAG([value], 1, 0) OVER (PARTITION BY id ORDER BY [age]) = 0
THEN 'Base'
WHEN [value] > LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY [age])
THEN 'Increasing'
WHEN [value] = LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY [age])
THEN 'No Change'
ELSE 'Decreasing'
END AS [Order]
FROM Test
價值觀
我設法得到一組 id 的 id 導致“翻轉：減少然后增加或相反”為：
例外案例
現在我想列印出與上面相同的結果，但有一串列示導致翻轉的行，如下所示（導致翻轉的行應該放在每個磁區的頂部）：
| ID | 年齡 | 價值 | 翻動 |
|---|---|---|---|
| 1 | 4 | 3 | 1 |
| 1 | 0 | 5 | 0 |
| 1 | 1 | 4 | 0 |
| 1 | 2 | 3 | 0 |
| 1 | 3 | 2 | 0 |
| 1 | 5 | 3 | 0 |
| 1 | 6 | 4 | 0 |
謝謝！
uj5u.com熱心網友回復：
擴展您現有的邏輯以獲取先前的訂單值，然后有條件地訂購
with cte as
(
SELECT
id, (2022 - age) yearId, age, [value],
CASE
WHEN LAG([value], 1, 0) OVER (PARTITION BY id ORDER BY [age]) = 0
THEN 'Base'
WHEN [value] > LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY [age])
THEN 'Increasing'
WHEN [value] = LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY [age])
THEN 'No Change'
ELSE 'Decreasing'
END AS [Order]
FROM T1
) ,
cte1 as
(select cte.*,concat(cte.[order], lag([order]) over (partition by id order by age)) concatlag
from cte)
select * ,
case when concatlag in('IncreasingDecreasing','DecreasingIncreasing') then 1 else 0 end
from cte1
order by
case when concatlag in('IncreasingDecreasing','DecreasingIncreasing') then 1 else 0 end desc,
age
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/492695.html