2023-12-21
请教个SQL
5M 有一表table_a,两个字段emid,dt,分别是工号和日期，需生成一个序号，按工号如果日期连续序号1,2,3...不连续就重新从1开始排，下图是例子，怎么生成这列？
我来答
添加附件
收藏
分享
问题补充
2条回答
默认
最新
with tmp1 as
(select a.*, dt-row_number() over (partition by emid order by dt) as dt_flag
from table_a a)
select b.emid,b.dt,row_number() over (partition by emid,dt_flag order by dt) as no
from tmp1 b
评论
有用 14
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
评论 有用 1回答交流
提交
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签，敬请期待~~
墨值悬赏