SQL 查询连接多个记录中的值

Question

我有一个包含雇员的表Employees（假设重要字段是ID int、Name varchar(50)）和一个包含销售区域的表Areas（ID int、EmployeeID int、USState char(2)）。

示例值是：

Employees
ID     Name
1      Shoeman
2      Smith
3      Johnson

Areas
ID     EmployeeID     USState
1      1              NY
2      1              FL
3      1              AR
4      2              DC
5      2              AR
6      3              TX

任何人都可以给我一个有关通过以下方式进行 SQL 查询来获取输出记录集的提示：

EmployeeID     USState
1              NY FL AR
2              DC AR
3              TX

目标平台：SQL Server 2005。

Answer 1

此操作称为 GROUP_CONCAT 在 MySQL 中，但 SQL Server 不支持它。

在 SQL Server 中，您可以使用 FOR XML PATH 来模拟相同的功能 黑客。

SELECT extern.EmployeeID, states AS USState
FROM Areas AS extern
CROSS APPLY (
    SELECT USState + ' '
    FROM Areas AS intern
    WHERE extern.EmployeeID = intern.EmployeeID
    FOR XML PATH('')
) T1 (states)
GROUP BY extern.EmployeeID, states
ORDER BY extern.EmployeeID

另一种方法是使用递归 CTE。这是一个不那么棘手的解决方案，但也更复杂：

WITH qs(EmployeeID, USState, rn, cnt) AS
(
    SELECT
        EmployeeID,
        USState,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY USState),
        COUNT(*) OVER (PARTITION BY EmployeeID)
    FROM    Areas
),
t (EmployeeID, prodname, gc, rn, cnt) AS
(
    SELECT EmployeeID, USState, CAST(USState AS NVARCHAR(MAX)), rn, cnt
    FROM qs
    WHERE rn = 1
    UNION ALL
    SELECT
        qs.EmployeeID, qs.USState,
        CAST(t.gc + ' ' + qs.USState AS NVARCHAR(MAX)),
        qs.rn, qs.cnt
    FROM t
    JOIN qs ON qs.EmployeeID = t.EmployeeID
           AND qs.rn = t.rn + 1
)
SELECT EmployeeID, gc AS USState
FROM   t
WHERE  rn = cnt
ORDER BY EmployeeID
OPTION (MAXRECURSION 0)

两种方法都会给出您想要的结果：

EmployeeID     USState
1              NY FL AR 
2              DC AR 
3              TX 

Answer 2

这是我首选的使用 UDF 的格式（在大型数据库上似乎也更快）

CREATEFUNCTION dbo.StateList(@ID int) RETURNS varchar(max)

AS

DECLARE @out varchar(max);

SET @out=''; -- comment this out to reutrn NULL if nothing found

SELECT @out=@out+USState+' ' -- operates like a loop assigning the values in sequence to the out variable.

FROM AREAS
WHERE EmployeeID=@ID
ORDER BY USState

RETURN @out

查询示例...

SELECT Name, dbo.StateList(EmployeeID)
FROM Employees
Order BY Name

通过这种方式，您可以为希望以这种方式分解的每个列表创建一个 UDF。