订单号 订单物料 订单数量
1001 aa 10
1001 zz 7
1002 ff 6
1002 dd 5
表2生产订单 物料 数量 订单号
2001 aa 5 1001
2001 bb 8 1001
2001 cc 9 1001
2002 gg 6 1002
想
1001 aa 10 2001 aa 5
1001 zz 7 2001 bb 8
‘’‘’‘’2001 cc 9
1002 ff 6 2002 gg 6
1002 dd 5 ‘’‘’‘’
结果能查出来,
现在想根据订单号,进行排序,这排序应该怎么写
1001 aa 10
1001 zz 7
1002 ff 6
1002 dd 5
表2生产订单 物料 数量 订单号
2001 aa 5 1001
2001 bb 8 1001
2001 cc 9 1001
2002 gg 6 1002
想
1001 aa 10 2001 aa 5
1001 zz 7 2001 bb 8
‘’‘’‘’2001 cc 9
1002 ff 6 2002 gg 6
1002 dd 5 ‘’‘’‘’
结果能查出来,
现在想根据订单号,进行排序,这排序应该怎么写
declare @t1 table([订单号] int,[订单物料] varchar(2),[订单数量] int)
insert @t1
select 1001,'aa',10 union all
select 1001,'zz',7 union all
select 1002,'ff',6 union all
select 1002,'dd',5declare @t2 table([生产订单] int,[物料] varchar(2),[数量] int,[订单号] int)
insert @t2
select 2001,'aa',5,1001 union all
select 2001,'bb',8,1001 union all
select 2001,'cc',9,1001 union all
select 2002,'gg',6,1002;with m1 as(
select *,row_number() over (partition by [订单号] order by (select 1)) as id from @t1)
,m2 as(
select *,row_number() over (partition by [订单号] order by (select 1)) as id from @t2)select
m1.[订单号],m1.[订单物料],m1.[订单数量],
m2.[生产订单],m2.[物料],m2.[数量],m2.[订单号]
from m1
full join m2 on m1.[订单号]=m2.[订单号] and m1.id=m2.id
/*
订单号 订单物料 订单数量 生产订单 物料 数量 订单号
----------- ---- ----------- ----------- ---- ----------- -----------
1001 aa 10 2001 aa 5 1001
1001 zz 7 2001 bb 8 1001
NULL NULL NULL 2001 cc 9 1001
1002 ff 6 2002 gg 6 1002
1002 dd 5 NULL NULL NULL NULL
*/
declare @t1 table([订单号] int,[订单物料] varchar(2),[订单数量] int)
insert @t1
select 1001,'aa',10 union all
select 1001,'zz',7 union all
select 1002,'ff',6 union all
select 1002,'dd',5declare @t2 table([生产订单] int,[物料] varchar(2),[数量] int,[订单号] int)
insert @t2
select 2001,'aa',5,1001 union all
select 2001,'bb',8,1001 union all
select 2001,'cc',9,1001 union all
select 2002,'gg',6,1002;with m1 as(
select *,row_number() over (partition by [订单号] order by (select 1)) as id from @t1)
,m2 as(
select *,row_number() over (partition by [订单号] order by (select 1)) as id from @t2)select
isnull(ltrim(m1.[订单号]),'') as [订单号],
isnull(m1.[订单物料],'') as [订单物料],
isnull(ltrim(m1.[订单数量]),'') as [订单数量],
isnull(ltrim(m2.[生产订单]),'') as [生产订单],
isnull(m2.[物料],'') as [物料],
isnull(ltrim(m2.[数量]),'') as [数量]
from m1
full join m2 on m1.[订单号]=m2.[订单号] and m1.id=m2.id
/*
订单号 订单物料 订单数量 生产订单 物料 数量
------------ ---- ------------ ------------ ---- ------------
1001 aa 10 2001 aa 5
1001 zz 7 2001 bb 8
2001 cc 9
1002 ff 6 2002 gg 6
1002 dd 5 (5 row(s) affected)
*/