select *
(
select 1 as col1, 1 as col2
union all
select 2, 2
union all
select 3, 2
union all
select 4, 1
union all
select 5, 3
union all
select 6, 3
union all
select 7, 2
) Q
我需要根據 col2 的可變性以及 col1 的行號。我需要檢查以前的行是否不同。
col1, col2, needed
1, 1, 1
2, 2, 2
3, 2, 2
4, 1, 3
5, 3, 4
6, 3, 4
7, 2, 5
uj5u.com熱心網友回復：
使用視窗函式應該不會太難：
SELECT col1, col2,
sum(newgr) OVER (ORDER BY col1, col2)
FROM (SELECT col1, col2,
CASE WHEN lag(col2) OVER (ORDER BY col1, col2)
IS DISTINCT FROM col2
THEN 1
ELSE 0
END AS newgr
FROM (VALUES (1, 1),
(2, 2),
(3, 2),
(4, 1),
(5, 3),
(6, 3),
(7, 2)
) AS q(col1,col2)
) AS p;
col1 │ col2 │ sum
══════╪══════╪═════
1 │ 1 │ 1
2 │ 2 │ 2
3 │ 2 │ 2
4 │ 1 │ 3
5 │ 3 │ 4
6 │ 3 │ 4
7 │ 2 │ 5
(7 rows)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/418786.html
標籤：