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;
select t.num,s.name,s.expect_finish_date from job s ,(select name,ROW_NUMBER() over(order by min(expect_finish_date) asc) num from jobgroup by name) t where s.name=t.name order by t.num,s.expect_finish_date 思路和2楼一样
(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;
,(select name,ROW_NUMBER() over(order by min(expect_finish_date) asc) num from jobgroup by name) t
where s.name=t.name
order by t.num,s.expect_finish_date
思路和2楼一样