删除状态未变化的连续数据,只保留状态变化的第一条数据；
数据如下：
job_id state
1 1
2 2
6 3
9 3
10 3
14 1
15 1
16 4
17 4
22 4
25 4
26 1
27 3
28 3
29 2
30 1
33 3
34 3
35 3
36 3
期望筛选后的数据
job_id state
1 1
2 2
6 3
14 1
16 4
26 1
27 3
29 2
30 1
33 3
SQL语句如下：
SELECT
a_job_id,
a_state
FROM (
SELECT
a.job_id AS a_job_id,
a.state AS a_state,
b.state AS b_state,
CASE WHEN a.state = b.state THEN 'D' ELSE '0' END AS flag
FROM
(
SELECT
ROW_NUMBER ()OVER(ORDER BY job_id DESC)AS row_id,
job_id,
state
FROM
demo.sys_job_po
)AS a
LEFT JOIN
(
SELECT
ROW_NUMBER ()OVER(ORDER BY job_id DESC)AS row_id,
job_id,
state
FROM
demo.sys_job_po
)AS b ON b.row_id = a.row_id+1
)AS t
WHERE flag = '0'
ORDER BY a_job_id ASC