SELECT
emid,dt,
CASE
WHEN dt - LAG(dt, 1, dt) OVER (PARTITION BY emid ORDER BY dt) = 1
THEN DENSE_RANK() OVER (PARTITION BY emid ORDER BY dt)
ELSE ROW_NUMBER() OVER (PARTITION BY emid ORDER BY dt)
END AS sequence_number
FROM
table_a
ORDER BY
emid, dt;

。。。。。oracle