时间戳

首页 > 解决方案 > 将 STRING_AGG 转换为 FOR XML PATH

sql - 将 STRING_AGG 转换为 FOR XML PATH

问题描述

我有一个带有嵌套选择的查询。我在 SQL Server 2017 中编写了此查询，现在我必须将其转换为在 SQL Server 2014 中使用。据我所知，STRING_AGG必须转换为FOR XML PATHand 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 返回：

（无效的对象名称“结果”。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 返回：

无效的对象名称“结果”。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使用此代码会不停地运行。

如何转换该查询？

标签： sqlsql-server

解决方案

CROSS APPLY的工作方式有点像嵌套版本的INNER JOIN.

虽然 anOUTER APPLY就像嵌套版本的 a 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 行受影响

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 行受影响

--
-- 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

文件搜索 | 塔拉菲萨巴
------------: | :-----------
           1 | 酒吧; 富   
           2 | 嗡嗡声        

--
-- 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

文件搜索 | 塔拉菲萨巴
------------: | :-----------
           1 | 酒吧; 富   
           2 | 嗡嗡声        
           3 | 无效的        

--
-- 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

文件搜索 | 塔拉菲萨巴
------------: | :-----------
           1 | 酒吧; 富   
           2 | 嗡嗡声        
           3 | 无效的

db<>在这里摆弄

推荐阅读