怎么创建 SQL Server 函数以 加入"子查询中的多行到单个分隔字段?
本教程将介绍如何创建 SQL Server 函数以“加入"子查询中的多行到单个分隔字段?的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。
问题描述
为了说明,假设我有两个表如下:
VehicleID Name
1Chuck
2Larry
LocationID VehicleID City
1 1New York
2 1Seattle
3 1Vancouver
4 2Los Angeles
5 2Houston
我想写一个查询返回以下结果:
VehicleID Name Locations
1ChuckNew York, Seattle, Vancouver
2LarryLos Angeles, Houston
我知道这可以使用服务器端游标来完成,即:
DECLARE @VehicleID int
DECLARE @VehicleName varchar(100)
DECLARE @LocationCity varchar(100)
DECLARE @Locations varchar(4000)
DECLARE @Results TABLE
(
VehicleID int
Name varchar(100)
Locations varchar(4000)
)
DECLARE VehiclesCursor CURSOR FOR
SELECT
[VehicleID]
, [Name]
FROM [Vehicles]
OPEN VehiclesCursor
FETCH NEXT FROM VehiclesCursor INTO
@VehicleID
, @VehicleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Locations = ''
DECLARE LocationsCursor CURSOR FOR
SELECT
[City]
FROM [Locations]
WHERE [VehicleID] = @VehicleID
OPEN LocationsCursor
FETCH NEXT FROM LocationsCursor INTO
@LocationCity
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Locations = @Locations + @LocationCity
FETCH NEXT FROM LocationsCursor INTO
@LocationCity
END
CLOSE LocationsCursor
DEALLOCATE LocationsCursor
INSERT INTO @Results (VehicleID, Name, Locations) SELECT @VehicleID, @Name, @Locations
ENDCLOSE VehiclesCursor
DEALLOCATE VehiclesCursor
SELECT * FROM @Results
但是,如您所见,这需要大量代码.我想要的是一个通用函数,它可以让我做这样的事情:
SELECT VehicleID
, Name
, JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations
FROM Vehicles
这可能吗?还是类似的?
推荐答案
如果您使用的是 SQL Server 2005,则可以使用 FOR XML PATH 命令.
SELECT [VehicleID]
, [Name]
, (STUFF((SELECT CAST(', ' + [City] AS VARCHAR(MAX))
FROM [Location]
WHERE (VehicleID = Vehicle.VehicleID)
FOR XML PATH ('')), 1, 2, '')) AS Locations
FROM [Vehicle]
这比使用游标容易得多,而且似乎工作得相当好.
更新
对于仍在使用此方法和较新版本的 SQL Server 的任何人,还有另一种方法,使用STRING_AGG
方法自 SQL Server 2017 起可用.
SELECT [VehicleID]
,[Name]
,(SELECT STRING_AGG([City], ', ')
FROM [Location]
WHERE VehicleID = V.VehicleID) AS Locations
FROM[Vehicle] V
这也允许将不同的分隔符指定为第二个参数,比前一种方法提供更多的灵活性.
好了关于怎么创建 SQL Server 函数以“加入"子查询中的多行到单个分隔字段?的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。