|
例子：
表ERPZKeyWord(产品表)
ID[int] keyword[varchar类型]
1 产品A
2 产品B
3 产品C
4 产品D
5 产品E
6 产品F
表ERPZSetMenu
ID[int] ProID(数据存入ERPZKeyWord关联ID)[varchar类型]
1 1,3,5
2 2,4,5
想要的结果是
1 产品A,产品C,产品E
2 产品B,产品D, 产品E
SQL:
select ID,
stuff((select ','+KeyWord from (select zsm.ID,zkw.KeyWord from ERPZSetMenu as zsm , ERPZKeyWord as zkw where zkw.id in(ProID)) as a where a.ID=t.ID for xml path('')),1,1,'') ProName
from (select zsm.ID from ERPZSetMenu as zsm , ERPZKeyWord as zkw where zkw.id in(ProID)) as t
这样写的结果是 报错： varchar 值 '1,3,5' 转换成数据类型 int 时失败。
有什么解决的办法么？
网上找到的
select * from dbo.ERPZKeyWord where
id in
(
select ProID from dbo.ERPZSetMenu
where charindex(ltrim(id), (select top 1 ProID from ERPZSetMenu ))>0
)
这种解决办法 还是不行？ 有哪位大神可以帮忙解决下？ create table ERPZKeyWord
(ID int,keyword varchar(10))
insert into ERPZKeyWord
select 1,'产品A' union all
select 2,'产品B' union all
select 3,'产品C' union all
select 4,'产品D' union all
select 5,'产品E' union all
select 6,'产品F'
create table ERPZSetMenu
(ID int,ProID varchar(10))
insert into ERPZSetMenu
select 1,'1,3,5' union all
select 2,'2,4,5'
with t as
(select a.ID,c.keyword 'k'
from ERPZSetMenu a
inner join master.dbo.spt_values b on b.type='P' and b.number between 1 and len(a.ProID)
and substring(','+a.ProID,b.number,1)=','
inner join ERPZKeyWord c on substring(a.ProID,b.number,charindex(',',a.ProID+',',b.number)-b.number)
=c.ID
)
select a.ID,
stuff((select ','+b.k
from t b
where b.ID=a.ID for xml path('')),1,1,'') 'ProKeyword'
from t a
group by a.ID
/*
ID ProKeyword
----------- --------------------
1 产品A,产品C,产品E
2 产品B,产品D,产品E
(2 row(s) affected)
*/
| 