我有一个如下的查询，其中内部查询返回以下结果集。我需要修改查询，以便在返回null的最后一行中，返回第一个emp_id。基本上，试图在最后一行的下一个empid中获得第一行的empid。我希望这是有道理的。欢迎提出任何想法。
注意：更新了查询，当我将事例逻辑添加到最后一列时，我得到错误ORA-01467：排序键太长
SELECT * FROM ( SELECT emp_id
, lead(emp_id) OVER ( ORDER BY join_dt DESC, emp_id desc ) AS next_emp_id
, lead(title) OVER ( ORDER BY join_dt DESC, emp_id desc ) AS next_title
, lead(join_dt) OVER ( ORDER BY join_dt DESC, emp_id desc ) AS next_join_dt
,lead(url_keyword) OVER ( ORDER BY publish_dt DESC, art_id desc ) AS next_url_keyword
FROM (
SELECT DISTINCT eca.emp_id, a.title, max(esa.join_dt) as join_dt,REGEXP_SUBSTR(REGEXP_REPLACE(LOWER(a.empword), '[^a-z0-9]+', '-'), '([a-z0-9]+-?){0,5}([a-z0-9]+)') as url_keyword
FROM emp_groups eca, emp a, emp_assignment esa
WHERE eca.emp_group_id = 9
AND eca.emp_id = a.emp_id
AND a.emp_id = esa.emp_id
GROUP BY eca.emp_id, a.title,REGEXP_SUBSTR(REGEXP_REPLACE(LOWER(a.empword), '[^a-z0-9]+', '-'), '([a-z0-9]+-?){0,5}([a-z0-9]+)')
UNION ALL
SELECT DISTINCT eca.emp_id, a.title, max(esa.join_dt) as join_dt, REGEXP_SUBSTR(REGEXP_REPLACE(LOWER(a.empword), '[^a-z0-9]+', '-'), '([a-z0-9]+-?){0,5}([a-z0-9]+)') as url_keyword
FROM emp_groups eca, emp_archive a, emp_assign_archive esa
WHERE eca.emp_group_id = 9
AND eca.emp_id = a.emp_id
AND a.emp_id = esa.emp_id
GROUP BY eca.emp_id, a.title,, REGEXP_SUBSTR(REGEXP_REPLACE(LOWER(a.empword), '[^a-z0-9]+', '-'), '([a-z0-9]+-?){0,5}([a-z0-9]+)')
UNION ALL
SELECT DISTINCT eca.emp_id, a.title, max(esa.join_dt) as join_dt ,REGEXP_SUBSTR(REGEXP_REPLACE(LOWER(a.empword), '[^a-z0-9]+', '-'), '([a-z0-9]+-?){0,5}([a-z0-9]+)') as url_keyword
FROM emp_groups eca, emp_archive a, emp_assignment esa
WHERE eca.emp_group_id = 9
AND eca.emp_id = a.emp_id
AND a.emp_id = esa.emp_id
GROUP BY eca.emp_id, a.title,, REGEXP_SUBSTR(REGEXP_REPLACE(LOWER(a.empword), '[^a-z0-9]+', '-'), '([a-z0-9]+-?){0,5}([a-z0-9]+)')
)
)
WHERE emp_id = 6046935
empid nextempid title date
6235396 6239577 radio engineer 8/5/2015
6239577 6219531 radar engineer 7/16/2015
6219531 6083501 software engineer 7/8/2015
6083501 6141972 Project manager 6/24/2015
6141972 6163194 QA engineer 6/1/2015
6163194 6142464 technician 5/18/2015
6142464 6046935 teacher 4/23/2015
6046935 (null) (null) (null)
要求的结果集
empid nextempid nexttitle nextdate
6235396 6239577 radio engineer 8/5/2015
6239577 6219531 radar engineer 7/16/2015
6219531 6083501 software engineer 7/8/2015
6083501 6141972 Project manager 6/24/2015
6141972 6163194 QA engineer 6/1/2015
6163194 6142464 technician 5/18/2015
6142464 6046935 teacher 4/23/2015
6046935 6235396 doctor 9/5/2015
您可以使用first_value()分析函数来查找行集的第一个值，然后在下一行的值为空时使用该值。
with sample_data as (select 6235396 emp_id, 'doctor' title, to_date('9/05/2015', 'mm/dd/yyyy') join_dt from dual union all
select 6239577 emp_id, 'radio engineer' title, to_date('08/05/2015', 'mm/dd/yyyy') join_dt from dual union all
select 6219531 emp_id, 'radar engineer' title, to_date('07/16/2015', 'mm/dd/yyyy') join_dt from dual union all
select 6083501 emp_id, 'software engineer' title, to_date('07/08/2015', 'mm/dd/yyyy') join_dt from dual union all
select 6141972 emp_id, 'Project manager' title, to_date('06/24/2015', 'mm/dd/yyyy') join_dt from dual union all
select 6163194 emp_id, 'QA engineer' title, to_date('06/01/2015', 'mm/dd/yyyy') join_dt from dual union all
select 6142464 emp_id, 'technician' title, to_date('05/18/2015', 'mm/dd/yyyy') join_dt from dual union all
select 6046935 emp_id, 'teacher' title, to_date('04/23/2015', 'mm/dd/yyyy') join_dt from dual)
-- end of setting up a subquery that mimicks a table with data in it. See sql below:
SELECT emp_id,
case when lead(emp_id) OVER (ORDER BY join_dt DESC, emp_id desc) is null
then first_value(emp_id) over (order by join_dt desc, emp_id desc
rows between unbounded preceding and current row)
else lead(emp_id) OVER (ORDER BY join_dt DESC, emp_id desc)
end next_emp_id,
case when lead(emp_id) OVER (ORDER BY join_dt DESC, emp_id desc) is null
then first_value(title) over (order by join_dt desc, emp_id desc
rows between unbounded preceding and current row)
else lead(title) OVER (ORDER BY join_dt DESC, emp_id desc)
end next_title,
case when lead(emp_id) OVER (ORDER BY join_dt DESC, emp_id desc) is null
then first_value(join_dt) over (order by join_dt desc, emp_id desc
rows between unbounded preceding and current row)
else lead(join_dt) OVER (ORDER BY join_dt DESC, emp_id desc)
end next_join_dt
from sample_data
order by join_dt desc,
emp_id desc;
EMP_ID NEXT_EMP_ID NEXT_TITLE NEXT_JOIN_DT
---------- ----------- ----------------- ------------
6235396 6239577 radio engineer 08/05/2015
6239577 6219531 radar engineer 07/16/2015
6219531 6083501 software engineer 07/08/2015
6083501 6141972 Project manager 06/24/2015
6141972 6163194 QA engineer 06/01/2015
6163194 6142464 technician 05/18/2015
6142464 6046935 teacher 04/23/2015
6046935 6235396 doctor 09/05/2015
上面的查询假设emp_id不能为null。