if OBJECT_ID('[order]') is not null drop table [order]gocreate table [order](order_uid int,order_no nvarchar(20),car_no nvarchar(20)) goif OBJECT_ID('CNTR') is not null drop table CNTRgocreate table CNTR(cntr_uid int,cntr_no nvarchar(20),order_uid int) goinsert into [order] values(1,'HKSZ0910230001 ','aaa')insert into [order] values(2,'HKSZ0910230002 ','aaa')insert into [order] values(3,'HKSZ0910230003 ','aaa')insert into CNTR values(1,'CNTRNO1',1)insert into CNTR values(2,'CNTRNO2',1)insert into CNTR values(3,'CNTRNO1',2)insert into CNTR values(4,'CNTRNO1',2)insert into CNTR values(5,'CNTRNO2',3)select order_no,cntr_nofrom [order] o inner join CNTR R on O.order_uid=R.order_uid--where o.order_no= '9999' --订单号---sql2005才能这样写Select order_no,cntr_no=isnull(stuff((select ','+rtrim(cntr_no) from CNTR where [order].order_uid=CNTR.order_uid for XML path('')),1,1,''),'')from [order]/*order_no cntr_no-------------------- --------------------HKSZ0910230001 CNTRNO1HKSZ0910230001 CNTRNO2HKSZ0910230002 CNTRNO1HKSZ0910230002 CNTRNO1HKSZ0910230003 CNTRNO2(5 行受影响)order_no cntr_no-------------------- ------------------HKSZ0910230001 CNTRNO1,CNTRNO2HKSZ0910230002 CNTRNO1,CNTRNO1HKSZ0910230003 CNTRNO2*/go---sql2000的话要用函数if OBJECT_ID('dbo.getStr') is not null drop function dbo.getStrgocreate function dbo.getStr(@order_uid int) returns nvarchar(200)as begin declare @str as nvarchar(200) select @str=isnull(@str,'')+cntr_no from CNTR where order_uid=@order_uid if @str <> '' select @str=right(@str,len(@str)-1) return @strend goSelect order_no,car_no=dbo.getStr(order_uid) from [order]/*order_no car_no-------------------- ---------------HKSZ0910230001 NTRNO1CNTRNO2HKSZ0910230002 NTRNO1CNTRNO1HKSZ0910230003 NTRNO2(3 行受影响)*/
