SQL-学习使用FOR XML PATH
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL-学习使用FOR XML PATH,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6982字,纯文字阅读大概需要10分钟。
内容图文
前言:本人SQL技术很烂,然后工作时间也不久,许多东西都还在学习中,说的不好的地方尽请谅解.
首先跟大家说一下我今天遇到的问题吧.
查出的数据有三列,第一列存放的是32位的GUID,Res_Name存放的是一个物资类型.Res_Data存放的是部门的GUID.我现在需要得到的数据是这样的.
首先大家可以看到.第一张图的Res_Data中有多个部门的GUID,中间用逗号隔开的.
我当时想到的愚蠢的办法就是
1 @MaterialTypeName nvarchar(200), 2@CentralizedNamenvarchar(200), 3@startint, 4@limitint, 5@totalCountint output 6AS 7BEGIN 8SET NOCOUNT ON; 9select10 ROW_NUMBER() over (orderby res_id asc) as RowNumber, 11*12into #List 13from14 UBIPlatform..T_RESOURCE 15WHERE Res_Parent_Code=‘741cdd2bef2e479f8c5dd35cf6e8bf2a‘161718declare@iint,@countint; 19declare@Centralizednvarchar(200); 20declare@List1table(id int, ResId nvarchar(50),ResName nvarchar(50),ResData nvarchar(50)); 21select@count=COUNT(*) from UBIPlatform..T_RESOURCE WHERE Res_Parent_Code=‘741cdd2bef2e479f8c5dd35cf6e8bf2a‘22set@i=123while@i<=@count24begin25if@iin (select RowNumber from #List) 26begin27set@Centralized=‘‘; 28select29@Centralized=@Centralized+‘,‘+LTRIM(Res_Name) 30from UBIPlatform.dbo.FN_GETMultiValTable( 31 (select32 Res_Data 33from34 UBIPlatform..T_RESOURCE 35where36 Res_Id=(select Res_Id from #List where RowNumber=@i))) ge 37innerjoin UBIPlatform..T_RESOURCE r on r.Res_Id=ge.nvalue 3839if@Centralized!=‘‘40begin41insertinto42@List143select44@i, 45 Res_Id, 46 Res_Name, 47 (RIGHT(@Centralized,LEN(@Centralized)-1)) 48from49 UBIPlatform..T_RESOURCE 50where Res_Id=(select Res_Id from #List where RowNumber=@i) 51end52else53begin54insertinto55@List156select57@i, 58 Res_Id, 59 Res_Name, 60@Centralized61from62 UBIPlatform..T_RESOURCE 63where Res_Id=(select Res_Id from #List where RowNumber=@i) 64end65end66set@i=@i+167end6869select ROW_NUMBER() over (orderby id asc) as RowNumber,*into #List2 from@List1where70 (@MaterialTypeNameisnullor@MaterialTypeName=‘‘or ResName like‘%‘+@MaterialTypeName+‘%‘) 71and (@CentralizedNameisnullor@CentralizedName=‘‘or ResData like‘%‘+@CentralizedName+‘%‘) 7273selecttop(@limit) *from #List2 where RowNumber >@startorderby RowNumber asc74select@totalCount=COUNT(1) from@List175END
这个是我开始写出来的一个.烂到不行.虽然是解决了我的需求.但是显而易见这种办法是不可取的.后来请教同事,跟我介绍了FOR XML PATH,我查阅了一下就是将查询结果集以XML形式展现.
select Res_Id,Res_Name,Res_Data from UBIPlatform..T_RESOURCE where Res_Parent_Code=‘741cdd2bef2e479f8c5dd35cf6e8bf2a‘FOR XML PATH
结果:
1 < row > 2 < Res_Id >239dbe35bd8446afb262f62712d8eb1b</Res_Id> 3<Res_Name>修理费-设备备件</Res_Name> 4<Res_Data>4BD2D7C9D91546B09BA4438EE583F682</Res_Data> 5</row> 6<row> 7<Res_Id>4d35c89868854d649963410a126b4c30</Res_Id> 8<Res_Name>低值易耗-计量器具</Res_Name> 9<Res_Data>4ADEEC453DE04910B0136593CBB4187C</Res_Data>10</row>11<row>12<Res_Id>4e74469a37894ea8a7ddd5e356433119</Res_Id>13<Res_Name>物料消耗-计算机耗材</Res_Name>14<Res_Data>4BD2D7C9D91546B09BA4438EE583F682,9C87FFAFD8D24B5BBEA3BF1221DD507B</Res_Data>15</row>16<row>17<Res_Id>608f30860c16430aa8b13f98df0ca9f3</Res_Id>18<Res_Name>物料消耗-水票</Res_Name>19<Res_Data></Res_Data>20</row>21<row>22<Res_Id>87a4cefa112241c1b648454e7b3682d9</Res_Id>23<Res_Name>低值易耗-工具及其他</Res_Name>24<Res_Data></Res_Data>25</row>26<row>27<Res_Id>c2908fe510dd476aa878622dd9d07c83</Res_Id>28<Res_Name>物料消耗-杂品</Res_Name>29<Res_Data></Res_Data>30</row>31<row>32<Res_Id>c9014727c9804c6e9df4cc1bc1487a84</Res_Id>33<Res_Name>劳动保护费-劳保用品</Res_Name>34<Res_Data>D5566FDCDBB448FAB4A48D20A2492626</Res_Data>35</row>36<row>37<Res_Id>d3397fdcb454440f88c9c4f9432b3f40</Res_Id>38<Res_Name>低值易耗-办公设施</Res_Name>39<Res_Data>9C87FFAFD8D24B5BBEA3BF1221DD507B</Res_Data>40</row>41<row>42<Res_Id>d8222bcaeaba460db945324cb0a93a23</Res_Id>43<Res_Name>修理费-计算机备件</Res_Name>44<Res_Data>4BD2D7C9D91546B09BA4438EE583F682</Res_Data>45</row>
那么,如何改变XML行节点的名称呢?代码如下:
select Res_Id,Res_Name,Res_Data from UBIPlatform..T_RESOURCE where Res_Parent_Code=‘741cdd2bef2e479f8c5dd35cf6e8bf2a‘FOR XML PATH(‘RESOURCE‘)
原来的行节点<row> 变成了我们在PATH后面括号()中,自定义的名称<RESOURCE>,结果如下:
< RESOURCE > < Res_Id >239dbe35bd8446afb262f62712d8eb1b</Res_Id><Res_Name>修理费-设备备件</Res_Name><Res_Data>4BD2D7C9D91546B09BA4438EE583F682</Res_Data></RESOURCE><RESOURCE><Res_Id>4d35c89868854d649963410a126b4c30</Res_Id><Res_Name>低值易耗-计量器具</Res_Name><Res_Data>4ADEEC453DE04910B0136593CBB4187C</Res_Data></RESOURCE><RESOURCE><Res_Id>4e74469a37894ea8a7ddd5e356433119</Res_Id><Res_Name>物料消耗-计算机耗材</Res_Name><Res_Data>4BD2D7C9D91546B09BA4438EE583F682,9C87FFAFD8D24B5BBEA3BF1221DD507B</Res_Data></RESOURCE><RESOURCE><Res_Id>608f30860c16430aa8b13f98df0ca9f3</Res_Id><Res_Name>物料消耗-水票</Res_Name><Res_Data></Res_Data></RESOURCE><RESOURCE><Res_Id>87a4cefa112241c1b648454e7b3682d9</Res_Id><Res_Name>低值易耗-工具及其他</Res_Name><Res_Data></Res_Data></RESOURCE><RESOURCE><Res_Id>c2908fe510dd476aa878622dd9d07c83</Res_Id><Res_Name>物料消耗-杂品</Res_Name><Res_Data></Res_Data></RESOURCE><RESOURCE><Res_Id>c9014727c9804c6e9df4cc1bc1487a84</Res_Id><Res_Name>劳动保护费-劳保用品</Res_Name><Res_Data>D5566FDCDBB448FAB4A48D20A2492626</Res_Data></RESOURCE><RESOURCE><Res_Id>d3397fdcb454440f88c9c4f9432b3f40</Res_Id><Res_Name>低值易耗-办公设施</Res_Name><Res_Data>9C87FFAFD8D24B5BBEA3BF1221DD507B</Res_Data></RESOURCE><RESOURCE><Res_Id>d8222bcaeaba460db945324cb0a93a23</Res_Id><Res_Name>修理费-计算机备件</Res_Name><Res_Data>4BD2D7C9D91546B09BA4438EE583F682</Res_Data></RESOURCE>
OK,接下来我就用这个FOR XML PATH用在我的问题上,
@MaterialTypeName nvarchar(200), @CentralizedNamenvarchar(200), @startint, @limitint, @totalCountint output ASBEGINSET NOCOUNT ON; select t_r.Res_Id as ResId, t_r.Res_Name as ResName, STUFF((select‘,‘+t_r1.Res_Name from UBIPlatform.dbo.T_RESOURCE t_r1 where t_r1.Res_Id in (select nvalue from UBIPlatform.dbo.FN_GETMultiValTable(t_r.Res_Data)) for xml path(‘‘)),1,1,‘‘) as ResData into #List1 from UBIPlatform..T_RESOURCE t_r where Res_Parent_Code=‘741cdd2bef2e479f8c5dd35cf6e8bf2a‘select ROW_NUMBER() over (orderby ResId asc) as RowNumber, *into #List2 from #List1 where (@MaterialTypeNameisnullor@MaterialTypeName=‘‘or ResName like‘%‘+@MaterialTypeName+‘%‘) and (@CentralizedNameisnullor@CentralizedName=‘‘or ResData like‘%‘+@CentralizedName+‘%‘) selecttop(@limit) *from #List2 where RowNumber >@startorderby RowNumber ascselect@totalCount=COUNT(1) from #List2 END
就这样,才几行的SQL就把我的问题解决了.
原文:http://www.cnblogs.com/xran/p/3944196.html
内容总结
以上是互联网集市为您收集整理的SQL-学习使用FOR XML PATH全部内容,希望文章能够帮你解决SQL-学习使用FOR XML PATH所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。