Apply的两个应用

来源：互联网发布：重装系统哪个软件好编辑：程序博客网时间：2024/05/18 03:26

APPLY比较实用，下面看两个应用。

1，字符连接

使用AdventureWorkDW2008R2

select c.ProductCategoryKey, b.namefrom dbo.DimProductCategory ccross apply(select ','+CONVERT(nvarchar(20),s.EnglishProductSubcategoryName)from dbo.DimProductSubcategory swhere c.ProductCategoryKey=s.ProductCategoryKeyorder bys.EnglishProductSubcategoryNamefor xml path (''),type) a(n)cross apply(select STUFF(a.n.value('./text()[1]','nvarchar(max)'),1,1,'')) b(name)order byc.ProductCategoryKey

结果如下

2，XML提取表

<students><student name="Bush"><subjects><subject name="Math" score="10"/><subject name="English" score="20"/><subject name="Science" score="30"/></subjects></student><student name="Obama"><subjects><subject name="Math" score="15"/><subject name="English" score="25"/><subject name="Science" score="35"/></subjects></student></students>

select t.student,t.subject,t.scorefrom @x.nodes('./students/student') student (rowset)cross apply student.rowset.nodes('./subjects/subject') subject (rowset)cross apply (select student.rowset.value('@name','nvarchar(20)'),       subject.rowset.value('@name','nvarchar(20)'),       subject.rowset.value('@score','int')       ) t (student,subject,score) order by     t.student asc,t.score desc;

结果如下