sql - 将 STRING_AGG 转换为 FOR XML PATH

Question

我有一个带有嵌套选择的查询。我在 SQL Server 2017 中编写了此查询，现在我必须将其转换为在 SQL Server 2014 中使用。据我所知 STRING_AGG必须转换为 FOR XML PATH和 STUFF ，我尝试了某种类型的查询，但找不到答案。

SQL Server 2017 中的原始工作查询是:

SELECT filesanadrow, STRING_AGG(CONVERT(nvarchar(MAX),result.tarafhesabsharh), ' ; ') AS tarafhesabha
FROM (SELECT DISTINCT k.row AS filesanadrow, CONVERT(NVARCHAR(MAX), t.tarafhesabsharh) AS tarafhesabsharh
FROM filesanad AS k INNER JOIN tarafhesab AS t ON k.row = t.filesanadrow) AS result
GROUP BY filesanadrow

然后 STRING_AGG(CONVERT(nvarchar(MAX),result.tarafhesabsharh), ' ; ')必须转换为 FOR XML PATH 和 STUFF。

我试过这个代码:

SELECT filesanadrow,
STUFF((SELECT distinct '' + t2.tarafhesabsharh
from result t2 where t1.filesanadrow = t2.filesanadrow 
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'') AS tarafhesabha
FROM (SELECT DISTINCT k.row AS filesanadrow, CONVERT(NVARCHAR(MAX), t.tarafhesabsharh) AS tarafhesabsharh
FROM filesanad AS k INNER JOIN tarafhesab AS t ON k.row = t.filesanadrow) AS result
GROUP BY filesanadrow 

但是 SQL Server 返回:

(Invalid object name 'result'. SQL2.sql 3 1 )

我也试过:

SELECT filesanadrow,
STUFF((SELECT '; ' + name FROM result  FOR XML PATH('')), 1, 1, '') AS tarafhesabha
FROM (SELECT DISTINCT k.row AS filesanadrow, CONVERT(NVARCHAR(MAX), t.tarafhesabsharh) AS tarafhesabsharh
FROM filesanad AS k INNER JOIN tarafhesab AS t ON k.row = t.filesanadrow) AS result
GROUP BY filesanadrow

但 SQL Server 返回:

Invalid object name 'result'. SQL2.sql 2 1

我再次尝试:

SELECT filesanadrow,
STUFF((SELECT '; ' + tarafhesabsharh FROM tarafhesab  FOR XML PATH('')), 1, 1, '') AS tarafhesabha
FROM (SELECT DISTINCT k.row AS filesanadrow, CONVERT(NVARCHAR(MAX), t.tarafhesabsharh) AS tarafhesabsharh
FROM filesanad AS k INNER JOIN tarafhesab AS t ON k.row = t.filesanadrow) AS result
GROUP BY filesanadrow

但是 SQL Server 转到 不停运转用这个代码。

如何转换该查询？

Answer 1

CROSS APPLY有点像 INNER JOIN 的嵌套版本.

而一个 OUTER APPLY就像 LEFT JOIN 的嵌套版本.

所以试试这个例子:

create table filesanad
(
 id int identity(1,1) primary key,
 [row] int
);

insert into filesanad values 
(1),(1),(2),(2),(3)
GO

5 rows affected

create table tarafhesab
(
 id int identity(1,1) primary key,
 filesanadrow int,
 tarafhesabsharh varchar(30)
);

insert into tarafhesab 
(filesanadrow, tarafhesabsharh) values
(1,'foo'),(1,'bar'),
(2,'buz');
GO

3 rows affected

--
-- CROSS APPLY on a FOR XML
-- 
SELECT f.filesanadrow, ca.tarafhesabha
FROM
(
    SELECT [row] AS filesanadrow
    FROM filesanad
    GROUP BY [row]
) f
CROSS APPLY
(
    SELECT STUFF(q.x.value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS tarafhesabha
    FROM
    (
        SELECT DISTINCT '; ' + t.tarafhesabsharh 
        FROM tarafhesab AS t
        WHERE t.filesanadrow = f.filesanadrow
        FOR XML PATH(''), type
    ) q(x)
    WHERE q.x IS NOT NULL
) ca;
GO

filesanadrow | tarafhesabha-----------: | :-----------           1 |  bar; foo              2 |  buz        

--
-- OUTER APPLY on a FOR XML
-- 
SELECT f.filesanadrow, ca.tarafhesabha
FROM
(
    SELECT [row] AS filesanadrow
    FROM filesanad
    GROUP BY [row]
) f
OUTER APPLY
(
    SELECT STUFF(q.x.value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS tarafhesabha
    FROM
    (
        SELECT DISTINCT '; ' + t.tarafhesabsharh 
        FROM tarafhesab AS t
        WHERE t.filesanadrow = f.filesanadrow
        FOR XML PATH(''), type
    ) q(x)
    WHERE q.x IS NOT NULL
) ca;
GO

filesanadrow | tarafhesabha-----------: | :-----------           1 |  bar; foo              2 |  buz                   3 | null        

--
-- nested query with a FOR XML
-- 
SELECT 
f.[row] AS filesanadrow, 
(
    SELECT STUFF(q.x.value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS tarafhesabha
    FROM
    (
        SELECT DISTINCT '; ' + t.tarafhesabsharh 
        FROM tarafhesab AS t
        WHERE t.filesanadrow = f.[row]
        FOR XML PATH(''), type
    ) q(x)
    WHERE q.x IS NOT NULL
) AS tarafhesabha
FROM filesanad f
GROUP BY f.[row]
GO

filesanadrow | tarafhesabha-----------: | :-----------           1 |  bar; foo              2 |  buz                   3 | null        

分贝<> fiddle here