为了说明,假设我有两个表,如下所示:
VehicleID Name 1 Chuck 2 Larry LocationID VehicleID City 1 1 New York 2 1 Seattle 3 1 Vancouver 4 2 Los Angeles 5 2 Houston
我想编写一个查询以返回以下结果:
VehicleID Name Locations 1 Chuck New York, Seattle, Vancouver 2 Larry Los 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 END CLOSE 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 以来可用的方法更容易且性能更高。
STRING_AGG
SELECT [VehicleID] ,[Name] ,(SELECT STRING_AGG([City], ', ') FROM [Location] WHERE VehicleID = V.VehicleID) AS Locations FROM [Vehicle] V
这也允许将不同的分隔符指定为第二个参数,从而比前一种方法提供更多的灵活性。