在当前的查询出来的数据上！如何进行 查找上一条和下一条！
当前代码
SELECT a.card_no ,a.Card_jlrq ,a.card_kehu_mc ,a.card_kehu_shouji,
a.gongsiNo ,a.gongsiMc ,MAX(b.work_no) work_no ,
b.xche_ssje ,min(c.wxxm_mc) wxxm_mc
FROM qpqxbs001.dbo.Card a
LEFT JOIN work_pz_sj b
ON a.card_no = b.card_no
LEFT JOIN work_mx_sj c ON b.work_no = c.work_no
WHERE a.gongsiNo='02' and a.Card_jlrq >='2014-02-01'
and a.Card_jlrq <='2014-02-20'
and not exists(select 1 from hyzfzgl.dbo.NewMemberVisit n
where a.card_no = n.card_no)
GROUP BY a.card_no ,a.Card_jlrq ,a.card_kehu_mc ,a.card_kehu_shouji,
a.gongsiNo,a.gongsiMc ,b.xche_ssje
ORDER BY card_jlrq DESC
------解决方案--------------------
试试这个：
;with t
as
(
SELECT a.card_no ,a.Card_jlrq ,a.card_kehu_mc ,a.card_kehu_shouji,
a.gongsiNo ,a.gongsiMc ,MAX(b.work_no) work_no ,
b.xche_ssje ,min(c.wxxm_mc) wxxm_mc ,
ROW_NUMBER() over(ORDER BY card_jlrq DESC) rownum
FROM qpqxbs001.dbo.Card a
LEFT JOIN work_pz_sj b
ON a.card_no = b.card_no
LEFT JOIN work_mx_sj c ON b.work_no = c.work_no
WHERE a.gongsiNo='02' and a.Card_jlrq >='2014-02-01'
and a.Card_jlrq <='2014-02-20'
and not exists(select 1 from hyzfzgl.dbo.NewMemberVisit n
where a.card_no = n.card_no)
GROUP BY a.card_no ,a.Card_jlrq ,a.card_kehu_mc ,a.card_kehu_shouji,
a.gongsiNo,a.gongsiMc ,b.xche_ssje
)
select *
from t
where rownum =(select rownum from t where work_no='WX0220140200074' )