为了进行说明,假设我有两个表,如下所示:
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
这个是可能的吗?或者类似的东西?
发布于 2008-08-10 13:15:20
注意,Matt's code将在字符串的末尾产生一个额外的逗号;使用COALESCE (或ISNULL ),如Lance的帖子中的链接所示,使用类似的方法,但不会留下额外的逗号需要删除。为了完整起见,下面是Lance在sqlteam.com上的链接中的相关代码:
DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(EmpUniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
发布于 2008-08-10 00:12:26
我不相信有一种方法可以在一个查询中做到这一点,但你可以使用临时变量来玩这样的把戏:
declare @s varchar(max)
set @s = ''
select @s = @s + City + ',' from Locations
select @s
它绝对比游标上的代码更少,而且可能更高效。
发布于 2011-01-06 23:20:41
在单个SQL查询中,不使用FOR XML子句。
公用表表达式用于递归连接结果。
-- rank locations by incrementing lexicographical order
WITH RankedLocations AS (
SELECT
VehicleID,
City,
ROW_NUMBER() OVER (
PARTITION BY VehicleID
ORDER BY City
) Rank
FROM
Locations
),
-- concatenate locations using a recursive query
-- (Common Table Expression)
Concatenations AS (
-- for each vehicle, select the first location
SELECT
VehicleID,
CONVERT(nvarchar(MAX), City) Cities,
Rank
FROM
RankedLocations
WHERE
Rank = 1
-- then incrementally concatenate with the next location
-- this will return intermediate concatenations that will be
-- filtered out later on
UNION ALL
SELECT
c.VehicleID,
(c.Cities + ', ' + l.City) Cities,
l.Rank
FROM
Concatenations c -- this is a recursion!
INNER JOIN RankedLocations l ON
l.VehicleID = c.VehicleID
AND l.Rank = c.Rank + 1
),
-- rank concatenation results by decrementing length
-- (rank 1 will always be for the longest concatenation)
RankedConcatenations AS (
SELECT
VehicleID,
Cities,
ROW_NUMBER() OVER (
PARTITION BY VehicleID
ORDER BY Rank DESC
) Rank
FROM
Concatenations
)
-- main query
SELECT
v.VehicleID,
v.Name,
c.Cities
FROM
Vehicles v
INNER JOIN RankedConcatenations c ON
c.VehicleID = v.VehicleID
AND c.Rank = 1
https://stackoverflow.com/questions/6899
复制相似问题