你的位置:首页 > 数据库

[数据库]SQLServer ForXmlPath简单应用


For

一:For

for

---SQL中FOR

1、AUTO模式:返回数据表为起表名的元素,每一列的值返回为属性;

SELECT * FROM tb_test for

2、RAW模式:返回数据行为元素,每一列的值作为元素的属性;

SELECT * FROM tb_test for

3、PATH模式:通过简单的XPath语法来允许用户自定义嵌套的

SELECT * FROM tb_test for

4、EXPLICIT模式:通过SELECT语法定义输出

 

二:For

Tab_users:

tab_items:

    1. For

      SELECT * FROM tab_users FOR

    2. For

      SELECT * FROM tab_users FOR

    3. For

      SELECT userID AS id,LastName AS 姓,FirstName AS 名 FROM dbo.tab_users FOR

    4. 终极大招

      SELECT userID AS id ,

              LastName AS 姓,

      FirstName AS 名,

              (SELECT CONCAT(itemName,',') FROM dbo.tab_items WHERE userId =1 FOR

      FROM dbo.tab_users

      WHERE userID = 1

      由上图查询结果可以看到我们通过 For

    5. 终极大招补充

      SELECT * ,

      LEFT(A.Items, LEN(A.Items) - 1) AS 物品

      FROM ( SELECT userID AS id ,

      LastName AS 姓 ,

      FirstName AS 名 ,

      ( SELECT CONCAT(itemName, ',')

      FROM dbo.tab_items

      WHERE userId = 1

      FOR

      ) AS Items

      FROM dbo.tab_users

      WHERE userID = 1

      ) AS A;

      查询结果: