17,086
社区成员
发帖
与我相关
我的任务
分享
with t as
(select 1 emp_no,
'A' name,
to_date('2014/07/31', 'yyyy/mm/dd') expect_finish_date
from dual
union all
select 2 emp_no,
'B' name,
to_date('2014/08/12', 'yyyy/mm/dd') expect_finish_date
from dual
union all
select 1 emp_no,
'A' name,
to_date('2014/08/02', 'yyyy/mm/dd') expect_finish_date
from dual
union all
select 3 emp_no,
'C' name,
to_date('2014/07/30', 'yyyy/mm/dd') expect_finish_date
from dual
union all
select 2 emp_no,
'B' name,
to_date('2014/08/02', 'yyyy/mm/dd') expect_finish_date
from dual
union all
select 3 emp_no,
'C' name,
to_date('2014/08/20', 'yyyy/mm/dd') expect_finish_date
from dual)
SELECT T1.EMP_NO, T1.NAME, T1.expect_finish_date, T2.RN
FROM T T1,
(SELECT NAME, ROWNUM RN
FROM (SELECT NAME, MIN(expect_finish_date) MIN_DT
FROM T
GROUP BY NAME
ORDER BY MIN(expect_finish_date))) T2
WHERE T1.NAME = T2.NAME
ORDER BY T2.RN, T1.expect_finish_date;