求最近的员工名称？
表
id name regtime 
1 张1 20120107
2 张2 20120102
3 张3 20120106
4 张4 20120104
5 张5 20120105
6 张6 20120105
7 张7 20120106
8 张8 20120107
9 张9 20120108
10 张10 20120109

求小于20120106时间最近的name值，与小于20120106时间总行数？？
有没有一条语句搞定的方法？
两条也可以贴出来

------解决方案--------------------
with t as
(
select 1 id,'张1' name, '20120107' regtime from dual union 
select 2 ,'张2', '20120102' from dual union 
select 3 ,'张3', '20120106' from dual union 
select 4 ,'张4', '20120104' from dual union 
select 5 ,'张5', '20120105' from dual union 
select 6 ,'张6', '20120105' from dual union 
select 7 ,'张7', '20120106' from dual union 
select 8 ,'张8', '20120107' from dual union 
select 9 ,'张9', '20120108' from dual union 
select 10 ,'张10', '20120109' from dual
)
select name, (select count(1) from t where regtime < '20120106') cou
from t
 where regtime in

(select '20120106' -
(select s
from (select min(b.regtime - a.regtime) s
from t a, t b
where a.id != b.id
and b.regtime = '20120106'
and a.regtime < b.regtime) tt) as regtime
from dual)

------解决方案--------------------
好久不写SQL 了，试试
不是很考虑数据量的SQL

SQL code

select name,total as 总数
from (
select name,
       sum(1) over(order by regtime  ) as total,
       row_number() over(order by regtime  desc) as rn,
       regtime  
  from tb
 where regtime  > 日期
 )
 where rn=1;

------解决方案--------------------

没看明白2楼的那一大段，改了一下，结果一样。

SQL code

with t as
(
select 1 id,'张1' name, '20120107' regtime from dual union 
select 2 ,'张2', '20120102' from dual union 
select 3 ,'张3', '20120106' from dual union 
select 4 ,'张4', '20120104' from dual union 
select 5 ,'张5', '20120105' from dual union 
select 6 ,'张6', '20120105' from dual union 
select 7 ,'张7', '20120106' from dual union 
select 8 ,'张8', '20120107' from dual union 
select 9 ,'张9', '20120108' from dual union 
select 10 ,'张10', '20120109' from dual
)
select name, (select count(1) from t where regtime < '20120106') cou
  from t
 where regtime in
  (select MAX(regtime) FROM t
          WHERE regtime < '20120106')