order ship
id,rdsno id,rdsno
1 SO05060001 1 SE05060012
2 SO05060002 2 SE05060013orderprod shiplst
id,zbid,code,sendnumb id,zbid,code,sendnumb,senddate, pid
1 1 Prod1 2000 1 1 Prod1 1000 2006-05-12 1
1 2 Prod2 2500 1 2 Prod1 1000 2006-05-13 1
2 2 Prod2 2500 2006-05-13 2
id,rdsno id,rdsno
1 SO05060001 1 SE05060012
2 SO05060002 2 SE05060013orderprod shiplst
id,zbid,code,sendnumb id,zbid,code,sendnumb,senddate, pid
1 1 Prod1 2000 1 1 Prod1 1000 2006-05-12 1
1 2 Prod2 2500 1 2 Prod1 1000 2006-05-13 1
2 2 Prod2 2500 2006-05-13 2
1 SO05060001
2 SO05060002
orderprod
id,zbid,code,sendnumb
1 1 Prod1 2000
1 2 Prod2 2500 ship
id,rdsno
1 SE05060012
2 SE05060013 shiplst
id,zbid,code,sendnumb,senddate, pid
1 2 Prod1 1000 2006-05-13 1
1 1 Prod1 1000 2006-05-12 1
2 2 Prod2 2500 2006-05-13 2
就是此四表
order.rdsno,code, 送货数合并的字符串
SO05060001,Prod1,1000/2006-05-12/1000/2006-05-13
SO05060002,Prod2,2500/2006-05-13
insert into a select 1,'SO05060001'
union all select 2,'SO05060002'create table b(id int,zbid int,code varchar(10),sendnumb int)
insert into b select 1,1,'Prod1',2000
union all select 1,2,'Prod2',2500create table c(id int,rdsno varchar(20))
insert into c select 1,'SE05060012'
union all select 2,'SE05060013'create table d(id int,zbid int,code varchar(10),sendnumb int,senddate varchar(10),pid int)
insert into d select 1,2,'Prod1',1000,'2006-05-13',1
union all select 1,1,'Prod1',1000,'2006-05-12',1
union all select 2,2,'Prod2',2500,'2006-05-13',2
gocreate function dbo.fc_str(@id int)
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+'/'+cast(sendnumb as varchar)+'/'+cast(senddate as varchar) from d where id=@id
return stuff(@sql,1,1,'')
end
goselect a.rdsno,d.code,dbo.fc_str(a.id) from a,d where a.id=d.id group by a.rdsno,d.code,a.iddrop table a,b,c,d
drop function dbo.fc_str
create table a(id int,rdsno varchar(20))
insert into a select 1,'SO05060001'
union all select 2,'SO05060002'create table b(id int,zbid int,code varchar(10),sendnumb int)
insert into b select 1,1,'Prod1',2000
union all select 1,2,'Prod2',2500create table c(id int,rdsno varchar(20))
insert into c select 1,'SE05060012'
union all select 2,'SE05060013'create table d(id int,zbid int,code varchar(10),sendnumb int,senddate varchar(10),pid int)
insert into d select 1,2,'Prod1',1000,'2006-05-13',1
union all select 1,1,'Prod1',1000,'2006-05-12',1
union all select 2,2,'Prod2',2500,'2006-05-13',2
gocreate function dbo.fc_str(@pid int)
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+'/'+cast(sendnumb as varchar)+'/'+cast(senddate as varchar) from d where pid=@pid
return stuff(@sql,1,1,'')
end
goselect a.rdsno,d.code,dbo.fc_str(d.pid) from a,d where a.id=d.pid group by a.rdsno,d.code,d.piddrop table a,b,c,d
drop function dbo.fc_str