首页 > 代码库 > full join 时通过辅助列序号列消除笛卡尔积重复列

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 aorder 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 aorder 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=http://www.mamicode.com/laReturn.dt_ClassData >技术分享
  

  

 

 

 

..

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