;with cte 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 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)sselect b.* from cte a,cte b where abs(a.card_no-b.card_no)<=1如果你的card_no是主键上面的写法就没问题, 如果不是, 那就用Row_number自己加一列
本帖最后由 DBA_Huangzj 于 2014-02-21 10:56:27 编辑
;with cte as( select row_number() over (order by getdate()) as m,* from ( 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) a
)
sselect b.* from cte a,cte b where abs(a.m-b.m)<=1
试试这个:;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' )
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)sselect
b.*
from
cte a,cte b
where abs(a.card_no-b.card_no)<=1如果你的card_no是主键上面的写法就没问题, 如果不是, 那就用Row_number自己加一列
;with cte as(
select row_number() over (order by getdate()) as m,* from (
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) a
)
sselect
b.*
from
cte a,cte b
where abs(a.m-b.m)<=1
这个是现在的程序截图,比如我进行第一条数据 点击回访 然后保存 会把第一条数据插入到另外一个表里面
,同时我程序会提示 进行下一条回访,这时候刚才回访的数据下一条就是我想要的!
如果是简单这样的话,用row_number()带出一个顺序id,但是不显示到界面,是程序内部用而已,然后用这个id来找上下数据,貌似就可以了吧。
是的!哦,按照那几个字段,来排序后,区分是上一条,还是下一条数据呢按照 work_no 区分上一条下一条
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' )