怎么创建 SQL Server 函数以 加入"子查询中的多行到单个分隔字段?

本教程将介绍如何创建 SQL Server 函数以“加入"子查询中的多行到单个分隔字段?的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

怎么创建 SQL Server 函数以 加入"子查询中的多行到单个分隔字段? 教程 第1张

问题描述

为了说明,假设我有两个表如下:

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 函数以“加入"子查询中的多行到单个分隔字段?的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。