sql 游标，一条id显示在多行数据

sql 求一个游标，一条id显示在多行数据
现在有以下要求：表1：
Tableone
TableoneID orderNo Ordernumber NUB1
随机                S001 X3600000001 2
随机        S001 X3600000002 2
随机        S002 X3600000003 6
随机        S002 X3600000004 6
随机        S003 X3600000005 6
随机        S003 X3600000006 8

表2：
Tabletwo
TabletwoID orderNo Deliveryitem NUB2
随机        S001 D3600000001 4
随机        S002 D3600000002 7
随机        S003 D3600000003 8
随机        S003 D3600000004 4
随机        S003 D3600000005 2

要做成表三的形式：
MergeTable
MergeTableID orderNo   Ordernumber NUB1 Deliveryitem    NUB2
随机                S001 X3600000001       2    D3600000001          4
随机                          X3600000002       2
随机                S002 X3600000003       6    D3600000002     7
随机                          X3600000004       6
随机                 S003 X3600000005       6      D3600000003        8
随机                          X3600000006       8      D3600000004       4
随机                                                               D3600000005   2

orderNo   是唯一的
表1的Ordernumber 和表2的Deliveryitem 是不确定的要生存表三的形式；可用游标，存储过程都可以。最好是MySQL中和sql2008中实现
要完整的sql语句，谢谢！
sql 游标，一条id显示在多行数据

------解决思路----------------------





--建表数据

create table #表1

(

	TableoneID nvarchar(2),

	orderNo varchar(20),

	Ordernumber varchar(20),

	NUB1 int

)


insert into #表1 values

('随机'        ,'S001',	'X3600000001',	2),

('随机'	       ,'S001',	'X3600000002',	2),

('随机'	       ,'S002',	'X3600000003',	6),

('随机'	       ,'S002',	'X3600000004',	6),

('随机'	       ,'S003',	'X3600000005',	6),

('随机'	       ,'S003',	'X3600000006',	8)




create table #表2

(

	TabletwoID nvarchar(2),

	orderNo varchar(20),

	Deliveryitem varchar(20),

	NUB2 int

)


insert into #表2 values

('随机',	       'S001',	'D3600000001',	4),

('随机',	       'S002',	'D3600000002',	7),

('随机',	       'S003',	'D3600000003',	8),

('随机',	       'S003',	'D3600000004',	4),

('随机',	       'S003',	'D3600000005',	2);


with cte as

(select ROW_NUMBER()over(partition by orderNO order by orderNo) id,* from #表1)

,cte1 as

(select ROW_NUMBER()over(partition by orderNO order by orderNo) id,* from #表2)

,cte2 as

(

	select 

	ROW_NUMBER()over(order by (select 1)) id,

	'随机' mergetabID

	,ISNULL(a.orderNo,b.orderNo) orderNo 

	,a.Ordernumber

	,a.NUB1

	,b.Deliveryitem

	,b.NUB2  

	from cte a

	  full join cte1 b   ON b.orderNo = a.orderNo and b.id = a.id

)

select *,neworderNo=(select case when id+1 = a.id and orderNo = a.orderNo then '' else a.orderNo end  from cte2 where id = a.id-1) 

into #t 

from cte2 a


select *,case when neworderNo is null then (select orderNo from #t where id =1) else neworderNo end  from #t

------解决思路----------------------

-- SQL Server

WITH /* 测试数据

Table1(TableoneID,orderNo,Ordernumber,NUB1)AS(

	SELECT NEWID(),'S001','X3600000001',2 UNION ALL

	SELECT NEWID(),'S001','X3600000002',2 UNION ALL

	SELECT NEWID(),'S002','X3600000003',6 UNION ALL

	SELECT NEWID(),'S002','X3600000004',6 UNION ALL

	SELECT NEWID(),'S003','X3600000005',6 UNION ALL

	SELECT NEWID(),'S003','X3600000006',8

)

	,Table2(TabletwoID,orderNo,Deliveryitem,NUB2)AS(

	SELECT NEWID(),'S001','D3600000001',4 UNION ALL

	SELECT NEWID(),'S002','D3600000002',7 UNION ALL

	SELECT NEWID(),'S003','D3600000003',8 UNION ALL

	SELECT NEWID(),'S003','D3600000004',4 UNION ALL

	SELECT NEWID(),'S003','D3600000005',2

), */

t1 AS (

    SELECT *,

           ROW_NUMBER()OVER(PARTITION BY orderNo ORDER BY orderNumber) rn

      FROM table1

)

,t2 AS (

    SELECT *,

           ROW_NUMBER()OVER(PARTITION BY orderNo ORDER BY Deliveryitem) rn

      FROM table2

)

,t3 AS (

    SELECT ISNULL(t1.orderNo,t2.orderNo) orderNo,

           ISNULL(t1.rn,t2.rn) rn,

           t1.Ordernumber,

           t1.NUB1,

           t2.Deliveryitem,

           t2.NUB2

      FROM t1

 FULL JOIN t2

        ON t1.orderNo = t2.OrderNo

       AND t1.rn = t2.rn

)

    SELECT NEWID() MergeTableID,

           CASE WHEN rn=1 THEN orderNo ELSE NULL END orderNo,

           Ordernumber,

           NUB1,

           Deliveryitem,

           NUB2

      FROM t3

  ORDER BY t3.orderNo, rn

MergeTableID                         orderNo Ordernumber        NUB1 Deliveryitem        NUB2

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

251314C2-74E3-4694-8851-A72534EF9CD7 S001    X3600000001           2 D3600000001            4

244F7509-5275-49C2-89FC-6DA58B69DC10 NULL    X3600000002           2 NULL                NULL

2D984B1F-8E75-47EB-BF56-28B357454BAB S002    X3600000003           6 D3600000002            7

8E4194A0-6502-482B-BC69-C16E733A9BCF NULL    X3600000004           6 NULL                NULL

35677FDF-19BB-4002-94A5-FC9F3389902F S003    X3600000005           6 D3600000003            8

B25E87A8-DED9-466F-BE5A-A16AB24311BC NULL    X3600000006           8 D3600000004            4

3646C166-C1F2-4C06-90C5-75760567495A NULL    NULL               NULL D3600000005            2

sql 游标，一条id显示在多行数据

相关推荐