两条记录为一组，分组后合并为一条记录

[zkj513703929]

一张表中有一下类似数据，请看下图

现在我想要实现下图效果：



简单描述：
transdept和transtodept这两个字段是转入转出字段，也就是说由A转到B，在由B转回A，这样的情况合并为一条记录，所有的逻辑都是这样的，存在多次由A转回B再有B转入A在的情况,

一下是表及数据创建语法
CREATE TABLE [dbo].[WardTrans](
[WSN] [INT] NOT NULL,
[CureNo] [INT] NULL,
[TransDate] [SMALLDATETIME] NULL,
[TransWard] [INT] NULL,
[TransDept] [INT] NULL,
[TransToWard] [INT] NULL,
[TransToDept] [INT] NULL,
[Operator] [INT] NULL
) ON

insert into dbo.WardTrans(WSN,CureNo,TransDate,TransWard,TransDept,TransToWard,TransToDept,Operator) values('22','10024','2017-10-12 13:27:00','72101','71210','72301','71290','720250')
insert into dbo.WardTrans(WSN,CureNo,TransDate,TransWard,TransDept,TransToWard,TransToDept,Operator) values('23','10024','2017-10-13 10:00:00','72301','71210','72101','71210','720246')
insert into dbo.WardTrans(WSN,CureNo,TransDate,TransWard,TransDept,TransToWard,TransToDept,Operator) values('24','10024','2017-10-24 13:58:00','72101','71210','72301','71290','720243')
insert into dbo.WardTrans(WSN,CureNo,TransDate,TransWard,TransDept,TransToWard,TransToDept,Operator) values('25','10024','2017-10-24 14:05:00','72301','71290','72101','71210','21032')

[zkj513703929]

没人回答啊

[二月十六]

WSN等于23这条也没有由B传到A啊

[zkj513703929]

引用 2 楼 sinat_28984567 的回复:

WSN等于23这条也没有由B传到A啊

有的，wsn=22: 720101转入 72301，然后wsn=23时又有72301转回72101

[RINK_1]

with cte
as
(select *,ROW_NUMBER() over (order by transdate) as seq from WardTrans)

select A.*,A.TransDate as lastupdatetime,B.TransDate as wardtransoutdate 
from cte A
join cte B on A.seq=B.seq-1 AND A.TransWard=B.TransToWard AND A.TransToWard=B.TransWard
where A.seq%2=1

[zkj513703929]

引用 4 楼 RINK_1 的回复:

with cte
as
(select *,ROW_NUMBER() over (order by transdate) as seq from WardTrans)

select A.*,A.TransDate as lastupdatetime,B.TransDate as wardtransoutdate 
from cte A
join cte B on A.seq=B.seq-1 AND A.TransWard=B.TransToWard AND A.TransToWard=B.TransWard
where A.seq%2=1

谢谢，总体上来说解决了我的需求，

[zkj513703929]

引用 5 楼 zkj513703929 的回复:

[quote=引用 4 楼 RINK_1 的回复:]

with cte
as
(select *,ROW_NUMBER() over (order by transdate) as seq from WardTrans)

select A.*,A.TransDate as lastupdatetime,B.TransDate as wardtransoutdate 
from cte A
join cte B on A.seq=B.seq-1 AND A.TransWard=B.TransToWard AND A.TransToWard=B.TransWard
where A.seq%2=1

谢谢，总体上来说解决了我的需求，[/quote] 补一句，提供了个思路

[zkj513703929]

引用 6 楼 zkj513703929 的回复:

[quote=引用 5 楼 zkj513703929 的回复:] [quote=引用 4 楼 RINK_1 的回复:]

with cte
as
(select *,ROW_NUMBER() over (order by transdate) as seq from WardTrans)

select A.*,A.TransDate as lastupdatetime,B.TransDate as wardtransoutdate 
from cte A
join cte B on A.seq=B.seq-1 AND A.TransWard=B.TransToWard AND A.TransToWard=B.TransWard
where A.seq%2=1

谢谢，总体上来说解决了我的需求，[/quote] 补一句，提供了个思路[/quote] 但不是很完美，我用表值函数结合着程序解决了，