如何Stuff和'For Xml Path'在Sql Server中工作

编程入门 行业动态 更新时间:2024-10-28 00:28:28
本文介绍了如何Stuff和'For Xml Path'在Sql Server中工作的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述

表格是:

+ ---- + ------ + | Id |名称| + ---- + ------ + | 1 | aaa | | 1 | bbb | | 1 | ccc | | 1 | ddd | | 1 | eee | + ---- + ------ +

/ p>

+ ---- + --------------------- + | Id | abc | + ---- + --------------------- + | 1 | aaa,bbb,ccc,ddd,eee | + ---- + --------------------- +

查询:

SELECT ID, abc = STUFF $ b(SELECT','+ name FROM temp1 FOR XML PATH('')),1,1,'') FROM temp1 GROUP BY id

此查询正常工作。但我只需要解释它是如何工作的,还是有任何其他或短的方法来做到这一点。

我很难理解这一点。

解决方案

这是它的工作原理:

1 。使用FOR XML获取XML元素字符串

将FOR XML PATH添加到查询结尾时,可以将查询结果作为XML元素输出, PATH参数中包含的元素名称。例如,如果我们运行以下语句:

SELECT','+ name FROM temp1 FOR XML PATH('')

通过传入一个空白字符串(FOR XML PATH ')),我们得到以下结果:

,aaa,bbb,ccc,ddd,eee

2。使用STUFF除去前导逗号

STUFF语句将一个字符串填充到另一个字符串中,替换第一个字符串中的字符,但是,只需删除结果值列表的第一个字符。

SELECT abc = STUFF(( SELECT' '+ NAME FROM temp1 FOR XML PATH('')),1,1,'') FROM temp1

STUFF 的参数为:

  • 要填充的字符串(在我们的例子中是带有前导逗号的完整名称列表)
  • 开始删除的位置
  • 要删除的字符数(1,为前导逗号) ul>

    所以我们最终得到:

    aaa,bbb,ccc,ddd ,eee

    3。加入ID以获取完整列表 >

    接下来,我们在temp表中的id列表中加入这个id,得到一个名为id的列表:

    SELECT ID,abc = STUFF((SELECT','+ name FROM temp1 t1 WHERE t1.id = t2.id FOR XML PATH('')),1,1,'')from temp1 t2 group by id;

    我们有我们的结果:

    ----------------------------------- | Id |名称| | --------------------------------- | 1 | aaa,bbb,ccc,ddd,eee | ---------------------------

    希望这有助于!

    Table is:

    +----+------+ | Id | Name | +----+------+ | 1 | aaa | | 1 | bbb | | 1 | ccc | | 1 | ddd | | 1 | eee | +----+------+

    Required output:

    +----+---------------------+ | Id | abc | +----+---------------------+ | 1 | aaa,bbb,ccc,ddd,eee | +----+---------------------+

    Query:

    SELECT ID, abc = STUFF( (SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, '' ) FROM temp1 GROUP BY id

    This query is working properly. But I just need the explanation how it works or is there any other or short way to do this.

    I am getting very confused to understand this.

    解决方案

    Here is how it works:

    1. Get XML element string with FOR XML

    Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained in the PATH argument. For example, if we were to run the following statement:

    SELECT ',' + name FROM temp1 FOR XML PATH ('')

    By passing in a blank string (FOR XML PATH('')), we get the following instead:

    ,aaa,bbb,ccc,ddd,eee

    2. Remove leading comma with STUFF

    The STUFF statement literally "stuffs" one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.

    SELECT abc = STUFF(( SELECT ',' + NAME FROM temp1 FOR XML PATH('') ), 1, 1, '') FROM temp1

    The parameters of STUFF are:

    • The string to be "stuffed" (in our case the full list of name with a leading comma)
    • The location to start deleting and inserting characters (1, we’re stuffing into a blank string)
    • The number of characters to delete (1, being the leading comma)

    So we end up with:

    aaa,bbb,ccc,ddd,eee

    3. Join on id to get full list

    Next we just join this on the list of id in the temp table, to get a list of IDs with name:

    SELECT ID, abc = STUFF( (SELECT ',' + name FROM temp1 t1 WHERE t1.id = t2.id FOR XML PATH ('')) , 1, 1, '') from temp1 t2 group by id;

    And we have our result:

    ----------------------------------- | Id | Name | |---------------------------------| | 1 | aaa,bbb,ccc,ddd,eee | -----------------------------------

    Hope this helps!

更多推荐

如何Stuff和'For Xml Path'在Sql Server中工作

本文发布于:2023-10-19 00:08:47,感谢您对本站的认可!
本文链接:https://www.elefans.com/category/jswz/34/1505833.html
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。
本文标签:工作   Xml   Stuff   Server   Sql

发布评论

评论列表 (有 0 条评论)
草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍!