2019獨(dú)角獸企業(yè)重金招聘Python工程師標(biāo)準(zhǔn)>>>
?
create table tableone
(TableoneID varchar(50),orderNo varchar(20),Ordernumber varchar(20),NUB1 int
)
insert into tableone values
(NEWID() ,'S001', 'X3600000001', 2),
(NEWID() ,'S001', 'X3600000002', 2),
( NEWID() ,'S002', 'X3600000003', 6),
( NEWID() ,'S002', 'X3600000004', 6),
(NEWID() ,'S003', 'X3600000005', 6),
(NEWID() ,'S003', 'X3600000006', 8)create table tabletwo
(TabletwoID varchar(50),orderNo varchar(20),Deliveryitem varchar(20),NUB2 int
)
insert into tabletwo values
(NEWID(), 'S001', 'D3600000001', 4),
(NEWID(), 'S002', 'D3600000002', 7),
(NEWID(), 'S003', 'D3600000003', 8),
(NEWID(), 'S003', 'D3600000004', 4),
(NEWID(), 'S003', 'D3600000005', 2);-- SQL Server
WITH /*
Table1(TableoneID,orderNo,Ordernumber,NUB1)AS(SELECT NEWID(),'S001','X3600000001',2 UNION ALLSELECT NEWID(),'S001','X3600000002',2 UNION ALLSELECT NEWID(),'S002','X3600000003',6 UNION ALLSELECT NEWID(),'S002','X3600000004',6 UNION ALLSELECT NEWID(),'S003','X3600000005',6 UNION ALLSELECT NEWID(),'S003','X3600000006',8
),Table2(TabletwoID,orderNo,Deliveryitem,NUB2)AS(SELECT NEWID(),'S001','D3600000001',4 UNION ALLSELECT NEWID(),'S002','D3600000002',7 UNION ALLSELECT NEWID(),'S003','D3600000003',8 UNION ALLSELECT NEWID(),'S003','D3600000004',4 UNION ALLSELECT NEWID(),'S003','D3600000005',2
), */
t1 AS (SELECT *,ROW_NUMBER()OVER(PARTITION BY orderNo ORDER BY orderNumber) rnFROM tableone
)
,t2 AS (SELECT *,ROW_NUMBER()OVER(PARTITION BY orderNo ORDER BY Deliveryitem) rnFROM tabletwo
)
,t3 AS (SELECT ISNULL(t1.orderNo,t2.orderNo) orderNo,ISNULL(t1.rn,t2.rn) rn,t1.Ordernumber,t1.NUB1,t2.Deliveryitem,t2.NUB2FROM t1FULL JOIN t2ON t1.orderNo = t2.OrderNoAND t1.rn = t2.rn
)SELECT NEWID() MergeTableID,CASE WHEN rn=1 THEN orderNo ELSE NULL END orderNo,Ordernumber,NUB1,Deliveryitem,NUB2FROM t3ORDER BY t3.orderNo, rn
以上內(nèi)容在sql2008中實(shí)現(xiàn)，為了效率就沒用游標(biāo)
轉(zhuǎn)載于:https://my.oschina.net/marcello/blog/412287
總結(jié)
以上是生活随笔為你收集整理的sql已经完成，生成表的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。
