SqlServer-STUFF拼接列表

最新推荐文章于 2024-05-08 16:42:46 发布

原创最新推荐文章于 2024-05-08 16:42:46 发布 · 8.7k 阅读

2 ·

CC 4.0 BY-SA版权

SqlServer 专栏收录该内容

11 篇文章

订阅专栏

博客主要介绍了数据库操作，包括创建OrderInfo和OrderProduct表，向这两个表插入数据，还展示了查询表数据的操作。此外，分别演示了同一张表数据拼接和不同表数据拼接的SQL语句，帮助读者掌握数据库表操作及数据拼接技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建OrderInfo 表

CREATE TABLE OrderInfo
(
OrderGuid uniqueidentifier not null,
LoginId varchar(50) not null,
)

创建OrderProduct表

CREATE TABLE OrderProduct
(
   ProductGuid uniqueidentifier not null,
   ProductName varchar(100) not null,
   OrderGuid uniqueidentifier not null,
)

向OrderInfo 表插入数据

insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),'15678975078')
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),'15678975078')
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),'15678971111')
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),'15678971111')
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),'15678971111')
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),'15678971111')
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),'15678971111')
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),'15678973333')

select * from OrderInfo

向OrderProduct表插入数据

insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),'黑人牙膏','6646A662-6570-4A0D-B359-1B45D8441BA6')
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),'玻尿酸补水喷雾','6646A662-6570-4A0D-B359-1B45D8441BA6')
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),'白色帆布鞋','4B800C54-FDD8-496C-8414-C7122F708AA9')
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),'手镯','918DAD4C-71BA-4DBB-BD1D-09B5A75320F8')
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),'信纸','918DAD4C-71BA-4DBB-BD1D-09B5A75320F8')
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),'米老鼠','918DAD4C-71BA-4DBB-BD1D-09B5A75320F8')
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),'矿泉水','F8E1499F-6EEC-4AE6-8E77-0166D95116E1')
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),'眼镜','F8E1499F-6EEC-4AE6-8E77-0166D95116E1')
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),'机柜','4B800C54-FDD8-496C-8414-C7122F708AA9')

select * from OrderProduct

同一张表数据的拼接

SELECT p2.LoginId, OrderGuid = STUFF((
SELECT ',' + convert(varchar(36),OrderGuid)
FROM OrderInfo AS p1
WHERE p1.LoginId = p2.LoginId
FOR XML PATH('')
), 1, 1, '') FROM OrderInfo AS p2
GROUP BY p2.LoginId

SELECT p2.OrderGuid, ProductName = STUFF((
SELECT ',' + ProductName
FROM OrderProduct AS p1
WHERE p1.OrderGuid = p2.OrderGuid
FOR XML PATH('')
), 1, 1, '') FROM OrderProduct AS p2
GROUP BY p2.OrderGuid

不同表数据的拼接

SELECT p2.OrderGuid,p2.LoginId, ProductName = STUFF((
SELECT ',' + ProductName
FROM OrderProduct AS p1
WHERE p1.OrderGuid = p2.OrderGuid
FOR XML PATH('')
), 1, 1, '') FROM OrderInfo AS p2
GROUP BY p2.OrderGuid,p2.LoginId