星空网 > 软件开发 > 数据库

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: SQLServer ForXmlPath简单应用

tab_items: SQLServer ForXmlPath简单应用

    1. For

      SELECT * FROM tab_users FOR

      SQLServer ForXmlPath简单应用

    2. For

      SELECT * FROM tab_users FOR

      SQLServer ForXmlPath简单应用

    3. For

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

      SQLServer ForXmlPath简单应用

    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

      SQLServer ForXmlPath简单应用

      由上图查询结果可以看到我们通过 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;

      SQLServer ForXmlPath简单应用

      查询结果:

      SQLServer ForXmlPath简单应用




原标题:SQLServer ForXmlPath简单应用

关键词:sql

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。
我的浏览记录
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流