create table Inventory ( InventId varchar(50), GoodId varchar(50) ) create table Good ( GoodId varchar(50), GoodName varchar(50) ) Insert into Inventory values('1','1;2;3;4;5') Insert into Inventory values('2','1;2;3') Insert into Inventory values('3','2;4;5') Insert into Good values('1','朝花夕拾') Insert into Good values('1','永州八记') Insert into Good values('1','呐喊') Insert into Good values('1','镜花缘') Insert into Good values('1','桃花扇')表我又打了一遍,就是这两个表了。
我没找到 ms sql 的论坛,在一个sql anywhere studio 里发的帖子也没几个人回。 大神 !求解答,虽然不符合范式,但问题出现了,总想有个答案。
Microsoft SQL Server Management Studio 10.50.1600.1 Microsoft 数据访问组件 (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 5.0 6.0 Microsoft Internet Explorer 8.0.7601.17514 Microsoft .NET Framework 2.0.50727.5456 操作系统 6.1.7601
SELECT InventId,GoodId, GoodsName=(SELECT stuff( (select ';'+ltrim(goodname) from good g where charindex(CONVERT(VARCHAR,g.GoodId)+';',i.GoodId+';')>0 for xml path('') ),1,1,'')) FROM Inventory i参见http://blog.csdn.net/qxyywy/article/details/7460475
楼主上面的good.GoodID是VARCHAR的 就可以不用转换了 SELECT InventId,GoodId, GoodsName=(SELECT stuff( (select ';'+ltrim(goodname) from good g where charindex(g.GoodId+';',i.GoodId+';')>0 for xml path('') ),1,1,'')) FROM Inventory i 再次跟个链接: http://blog.csdn.net/qxyywy/article/details/7460475
给我完整版,不过觉得没什么意思 这个SQLSELECT InventId, GoodId=(SELECT stuff( (select ';10'+ltrim(goodID) from good g where charindex(g.GoodId+';',i.GoodId+';')>0 for xml path('') ),1,1,'')), GoodsName=(SELECT stuff( (select ';'+ltrim(goodname) from good g where charindex(g.GoodId+';',i.GoodId+';')>0 for xml path('') ),1,1,'')) FROM Inventory i
点击这里下载CLR函数创建脚本 之后语句执行如下: SELECT A.InventId,A.GoodId,dbo.Concatenate(C.GoodName) FROM Inventory A OUTER APPLY GetCodeTable(A.GoodId,';') B,Good C WHERE B.CODE=C.GoodId GROUP BY A.InventId,A.GoodId ORDER BY 1 聚合函数默认使用的逗号分隔符,如果一定要分号另外再说,要增加个自定义分隔符的聚合函数才行。
Insert into Goods values('1','朝花夕拾') Insert into Goods values('2','永州八记') Insert into Goods values('3','呐喊') Insert into Goods values('4','镜花缘') Insert into Goods values('5','桃花扇')SELECT InventId, GoodsName=(SELECT stuff( (select ';'+ltrim(GoodsName) from Goods g where charindex(CONVERT(VARCHAR,g.GoodsId)+';',i.GoodsId+';')>0 for xml path('') ),1,1,'')) FROM Inventory i 楼主,O了.
(
InventId varchar(50),
GoodId varchar(50)
)
create table Good
(
GoodId varchar(50),
GoodName varchar(50)
)
Insert into Inventory values('1','1;2;3;4;5')
Insert into Inventory values('2','1;2;3')
Insert into Inventory values('3','2;4;5')
Insert into Good values('1','朝花夕拾')
Insert into Good values('1','永州八记')
Insert into Good values('1','呐喊')
Insert into Good values('1','镜花缘')
Insert into Good values('1','桃花扇')表我又打了一遍,就是这两个表了。
大神 !求解答,虽然不符合范式,但问题出现了,总想有个答案。
这个可以参考,不过依然麻烦
Microsoft 数据访问组件 (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 8.0.7601.17514
Microsoft .NET Framework 2.0.50727.5456
操作系统 6.1.7601
GoodsName=(SELECT stuff( (select ';'+ltrim(goodname) from good g
where charindex(CONVERT(VARCHAR,g.GoodId)+';',i.GoodId+';')>0 for xml path('') ),1,1,''))
FROM Inventory i参见http://blog.csdn.net/qxyywy/article/details/7460475
楼主上面的good.GoodID是VARCHAR的 就可以不用转换了
SELECT InventId,GoodId,
GoodsName=(SELECT stuff( (select ';'+ltrim(goodname) from good g
where charindex(g.GoodId+';',i.GoodId+';')>0 for xml path('') ),1,1,''))
FROM Inventory i
再次跟个链接:
http://blog.csdn.net/qxyywy/article/details/7460475
charindex('1','1;21;31')>0
的情况?
没有问题的 请你看清楚 charindex(g.GoodId+';',i.GoodId+';')>0
GoodId=(SELECT stuff( (select ';10'+ltrim(goodID) from good g
where charindex(g.GoodId+';',i.GoodId+';')>0 for xml path('') ),1,1,'')),
GoodsName=(SELECT stuff( (select ';'+ltrim(goodname) from good g
where charindex(g.GoodId+';',i.GoodId+';')>0 for xml path('') ),1,1,''))
FROM Inventory i
之后语句执行如下:
SELECT A.InventId,A.GoodId,dbo.Concatenate(C.GoodName) FROM Inventory A OUTER APPLY GetCodeTable(A.GoodId,';') B,Good C
WHERE B.CODE=C.GoodId
GROUP BY A.InventId,A.GoodId
ORDER BY 1
聚合函数默认使用的逗号分隔符,如果一定要分号另外再说,要增加个自定义分隔符的聚合函数才行。
Insert into Goods values('2','永州八记')
Insert into Goods values('3','呐喊')
Insert into Goods values('4','镜花缘')
Insert into Goods values('5','桃花扇')SELECT InventId,
GoodsName=(SELECT stuff( (select ';'+ltrim(GoodsName) from Goods g
where charindex(CONVERT(VARCHAR,g.GoodsId)+';',i.GoodsId+';')>0 for xml path('') ),1,1,''))
FROM Inventory i
楼主,O了.
运行结果:
1 1;2;3;4;5 朝花夕拾,永州八记,呐喊,镜花缘,桃花扇
2 1;2;3 朝花夕拾,永州八记,呐喊
3 2;4;5 永州八记,镜花缘,桃花扇这个有难度啊 对于里面的内置函数不太了解 看你的那个SQL有些压力
难道我还来忽悠人?
我的SQL是2005的
select a.*,(select wmsys.wm_concat(GoodName) from good b where instr(a.goodid,b.GoodId)>0 ) from Inventory a