如果没有序号列,那么如果领灯表里有3条数据,还灯表里面有2条数据,full join后就是3*2=6条数据
--1、领灯表,每天每班每人允许重复数据 select ID ,ROW_NUMBER() over(partition by PersonID,classid,dt_ClassData order by id) as getnum ,[PersonID] ,[ClassID] ,[dt_GetTime] ,[dt_ClassData] from m_LampHistoryDataGet as a order by PersonID,dt_GetTime
![](http://images0.cnblogs.com/blog/452618/201501/151628520896962.png)
--2、还灯表,每天每班每人允许重复数据 select ID ,ROW_NUMBER() over(partition by PersonID,classid,dt_ClassData order by id) as getnum ,[PersonID] ,[ClassID] ,[dt_ReturnTime] ,[dt_ClassData] from v_LampHistoryDataReturn as a order by PersonID,dt_ReturnTime
![](http://images0.cnblogs.com/blog/452618/201501/151631557617841.png)
--3、整合表,要求领灯时间与还灯时间按照顺序一一对应,第一条领灯数据对应第一条还灯数据 SELECT laGet.id as GetID ,laReturn.ID as ReturnID ,coalesce(laGet.getnum,laReturn.returnnum) as num ,coalesce(laGet.PersonID,laReturn.PersonID) as PersonID ,laGet.[dt_GetTime] ,laReturn.dt_ReturnTime ,coalesce(laGet.ClassID,laReturn.ClassID) as classid ,coalesce(laGet.dt_ClassData,laReturn.dt_ClassData) as dt_ClassData FROM dbo.v_LampHistoryDataGet AS laGet full join dbo.v_LampHistoryDataReturn laReturn on laGet.PersonID=laReturn.PersonID and laGet.dt_ClassData=laReturn.dt_ClassData and laGet.ClassID=laReturn.ClassID and laGet.getnum= laReturn.returnnum order by PersonID,coalesce(dt_GetTime,dt_ReturnTime) --按照人员,领灯时间排序 END
![](http://images0.cnblogs.com/blog/452618/201501/151632118089311.png)
..