select a.住院号,a.住院次数,a.住院日期,b.住院日期 as 下次入院日期
from
(select *,ROW_NUMBER() over(order by 住院日期) rn from tb)a
left join (select *,ROW_NUMBER() over(order by 住院日期) rn from tb)b
on a.rn+1=b.rn
--住院号 住院次数 住院日期
WITH a1 (a,b,c) as
(
SELECT 522041,1,'2013-5-6 9:01' UNION all
SELECT 522041,2,'2013-5-7 17:01' UNION all
SELECT 522041,3,'2013-5-7 19:13'
)
SELECT *,ISNULL((SELECT TOP 1 c FROM a1 WHERE b>a.b ORDER BY b asc),'') d
FROM a1 a