with tt as( select 1 id, '张' name from dual union all select 1 id, '王' name from dual union all select 1 id, '李' name from dual union all select 2 id, '赵' name from dual)
SELECT nullif(id, pid) id, NAME FROM (SELECT id, NAME, lag(id) over(PARTITION BY id ORDER BY id) pid FROM tt)
with tt as( select 1 id, '张' name from dual union all select 1 id, '王' name from dual union all select 1 id, '李' name from dual union all select 2 id, '赵' name from dual) select decode(row_number() over(partition by id order by id), 1, id), name from ttDECODE(ROW_NUMBER()OVER(PARTIT NAME ------------------------------ ---- 1 张 王 李 2 赵
select 1 id, '张' name from dual union all
select 1 id, '王' name from dual union all
select 1 id, '李' name from dual union all
select 2 id, '赵' name from dual)
SELECT nullif(id, pid) id, NAME
FROM (SELECT id, NAME, lag(id) over(PARTITION BY id ORDER BY id) pid
FROM tt)
with tt as(
select 1 id, '张' name from dual union all
select 1 id, '王' name from dual union all
select 1 id, '李' name from dual union all
select 2 id, '赵' name from dual)
select decode(row_number() over(partition by id order by id), 1, id),
name
from ttDECODE(ROW_NUMBER()OVER(PARTIT NAME
------------------------------ ----
1 张
王
李
2 赵