full join 时通过辅助列序号列消除笛卡尔积重复列

如果没有序号列，那么如果领灯表里有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

--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

--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 

　　

 

 

 

..

转载于:https://www.cnblogs.com/xiaochun126/p/4226646.html