34,590
社区成员
发帖
与我相关
我的任务
分享
--方法是有的,利用xml来实现,程式看起来比较繁琐,不易读
--你可以用以下的例子修改下,原理一样
create table tba(ID int,classid varchar(20))
insert into tba values(1,'1,2,3')
insert into tba values(2,'2,3' )
insert into tba values(3,'1,3' )
create table tbb(ID varchar(10), classname nvarchar(10))
insert into tbb values('1',N'衣服')
insert into tbb values('2',N'上衣')
insert into tbb values('3',N'裤子')
go
SELECT id , classname FROM
(
SELECT DISTINCT id FROM (select tbc.id , tbb.classname from
(
SELECT A.id , B.classid FROM(SELECT id, [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
OUTER APPLY(SELECT classid = N.v.value('.', 'nvarchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
) tbc , tbb where tbc.classid = tbb.id
) T
)A
OUTER APPLY
(
SELECT [classname]= STUFF(REPLACE(REPLACE((
SELECT classname FROM (select tbc.id , tbb.classname from
(
SELECT A.id , B.classid FROM(SELECT id , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
OUTER APPLY(SELECT classid = N.v.value('.', 'nvarchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
) tbc , tbb where tbc.classid = tbb.id
) N
WHERE id = A.id
FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, '')
)N
order by id
drop table tba,tbb
/*
id classname
----------- --------------
1 衣服,上衣,裤子
2 上衣,裤子
3 衣服,裤子
*/
--这样设计感觉欠妥,试试临时表更新吧
WITH CET AS (
select 'C0001'PromotionCode, NULL TYPE, NULL DESC0, 'FALSE' DESC01 UNION all
select 'C0001', N'买数量送百分比数量', N'买数量送百分比数量', 'False' UNION all
select 'C0001', N'合同金额 True 销售数量',N'销售数量', 'True' UNION all
select 'C0001', N'合同金额 True 销售数量',N'合同金额', 'False'
)
select *, DESC02 = convert(nvarchar(50),[TYPE]) into #T from CET
while (exists(select 1 from #T ,(select DESC0,DESC01 from #T) as X where charindex(X.DESC0,#T.DESC02)>0))
begin
update #T
set DESC02 = replace(#T.DESC02,X.DESC0,X.DESC01)
from #T,(select DESC0,DESC01 from #T) as X
where charindex(X.DESC0,#T.DESC02)>0
end
select * from #T
/*
C0001 NULL NULL FALSE NULL
C0001 买数量送百分比数量 买数量送百分比数量 False False
C0001 合同金额 True 销售数量 销售数量 True False True True
C0001 合同金额 True 销售数量 合同金额 False False True True
*/
drop table #T