版本:SQL2000 求重复字段内容取数,非重复字段内容取数
表saledetail_z
billid ,itemno , vipmaterialid,viptraderid,vipbmclid, re1
1001 , 1 , 1 , 2 , 0 ,
1038 , 1 , 33 , 3 , 0 ,视图:
vip_lljgstorejcfkcpd
materialid,traderid,vipbmclid
1 , 2 , 1111
1 , 2 , 2222
1 , 2 , 3333
1 , 2 , 444433 , 3 , ABCD
当:saledetail_z.vipmaterialid=vip_lljgstorejcfkcpd.materialid
and saledetail_z.viptraderid=vip_lljgstorejcfkcpd.traderid
当且当 vip_lljgstorejcfkcpd.vipbmclid 有多个值时 在re1取多个值,
当 vip_lljgstorejcfkcpd.vipbmclid 唯一值写入到 vipbmclid要求如果:表saledetail_z
billid ,itemno , vipmaterialid,viptraderid,vipbmclid, re1
1001 , 1 , 1 , 2 , 0 ,'111,222,333,444'
1038 , 1 , 33 , 3 , ABCD ,
表saledetail_z
billid ,itemno , vipmaterialid,viptraderid,vipbmclid, re1
1001 , 1 , 1 , 2 , 0 ,
1038 , 1 , 33 , 3 , 0 ,视图:
vip_lljgstorejcfkcpd
materialid,traderid,vipbmclid
1 , 2 , 1111
1 , 2 , 2222
1 , 2 , 3333
1 , 2 , 444433 , 3 , ABCD
当:saledetail_z.vipmaterialid=vip_lljgstorejcfkcpd.materialid
and saledetail_z.viptraderid=vip_lljgstorejcfkcpd.traderid
当且当 vip_lljgstorejcfkcpd.vipbmclid 有多个值时 在re1取多个值,
当 vip_lljgstorejcfkcpd.vipbmclid 唯一值写入到 vipbmclid要求如果:表saledetail_z
billid ,itemno , vipmaterialid,viptraderid,vipbmclid, re1
1001 , 1 , 1 , 2 , 0 ,'111,222,333,444'
1038 , 1 , 33 , 3 , ABCD ,
go
create table [saledetail_z] (billid int,itemno int,vipmaterialid int,viptraderid int,vipbmclid int,re1 nvarchar(100))
insert into [saledetail_z]
select 1001,1,1,2,0,null union all
select 1038,1,33,3,0,''
if object_id('[vip_lljgstorejcfkcpd]') is not null drop table [vip_lljgstorejcfkcpd]
go
create table [vip_lljgstorejcfkcpd] (materialid int,traderid int,vipbmclid nvarchar(8))
insert into [vip_lljgstorejcfkcpd]
select 1,2,'1111' union all
select 1,2,'2222' union all
select 1,2,'3333' union all
select 1,2,'4444' union all
select 33,3,'ABCD'
select * from [saledetail_z]
select * from [vip_lljgstorejcfkcpd]
SELECT A.*, B. vipbmclid
FROM [saledetail_z] A
INNER JOIN ( SELECT materialid ,
traderid ,
vipbmclid = STUFF(( SELECT ',' + vipbmclid
FROM [vip_lljgstorejcfkcpd]
WHERE materialid = t.materialid
AND traderid = t.traderid
FOR
XML PATH('')
), 1, 1, '')
FROM [vip_lljgstorejcfkcpd] t
GROUP BY materialid ,
traderid
) B ON
A.vipmaterialid=B.materialid
and A.viptraderid=B.traderid
/*
billid itemno vipmaterialid viptraderid vipbmclid re1 vipbmclid
----------- ----------- ------------- ----------- ----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1001 1 1 2 0 NULL 1111,2222,3333,4444
1038 1 33 3 0 ABCD(2 行受影响)
*/
XML PATH('')请问这个在SQL2000适合用吗?可以用update 语句直接更新saledetail_z吗?直接update就可以了