sql server full join拼接表数据，按组加序号

--查询所有数据
select A.*,B.* from
(select z.id,z.requestId,z.FBillNo,dt5.FCauseAndProcess,dt5.FEquipmentNo,dt2.FDetail,row_number() over (PARTITION BY z.id ORDER BY z.id) RN
from formtable z
left join formtable2 dt2 on dt2.mainid=z.id
left join formtable5 dt5 on dt5.FEquipmentNo=dt2.FEquipmentNo
) A
FULL JOIN
(select dt1.mainid,dt1.FCustomer,dt1.FDemand,row_number() over (PARTITION BY dt1.mainid ORDER BY dt1.mainid) RN
from formtable1 dt1) B
on A.RN=B.RN and A.id=B.mainid ;

FULL JOIN ：全连接，相当于把左右两张表做拼接，横向拼接。

--------------------------------------------------------------------------------------------------------------------------

 

-----------------------------------------------------------------------------------------------------------------------

 

 

-------------------------------------------------------------------------------------------------------------------------

row_number() over (PARTITION BY z.id ORDER BY z.id) RN

按id分组，按id排序，加序号

如果不按组加序号，要这样写：row_number() over (order by DATALENGTH(dt1.mainid)) RN