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:
- For
SELECT * FROM tab_users FOR
- For
SELECT * FROM tab_users FOR
- For
SELECT userID AS id,LastName AS 姓,FirstName AS 名 FROM dbo.tab_users FOR
- 终极大招
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
- 终极大招补充
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简单应用
关键词:sql