求最近的员工名称？
表
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
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楼的那一大段，改了一下，结果一样。
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')