代码之家 › 专栏 › 技术社区 › Prashant Pimpale Dila Gurung

无法在SQL Server中将XML路径与UNION ALL一起使用

xml sql-server

Prashant Pimpale Dila Gurung · 技术社区 · 6 年前

UNION ALL 但一旦我申请 全部联合

SELECT ISNULL(STUFF((
SELECT plant from (
SELECT DISTINCT ', ' + Plant as Plant
FROM ASP_MstSKUPlantMapping SKUPM 
WHERE SKUID = '702953' AND Plant LIKE 'P%'
UNION ALL
SELECT DISTINCT ', ' + Plant as Plant
FROM ASP_MstSKUPlantMapping SKUPM 
WHERE SKUID = '702953' AND Plant LIKE 'S%'
) Temp

FOR XML PATH('')), 1, 2,''),'')

电流输出:

lant>, PN05</plant><plant>, PN10</plant>

PN05, PN10

编辑:如果删除 as Plant 此别名则显示错误为:

No column name was specified for column 1 of 'Temp'.

1 回复 | 直到 6 年前

LukStorms 6 年前

我认为你不需要用工会来解决这个问题。
它来自同一个表,所以它只是WHERE子句要处理的内容。

SELECT 
 ISNULL(STUFF((
    SELECT CONCAT(', ', Plant)
    FROM ASP_MstSKUPlantMapping SKUPM
    WHERE SKUID = '702953' AND Plant LIKE '[PS]%'
    GROUP BY Plant
    ORDER BY Plant
    FOR XML PATH('')
 ), 1, 2, ''),'') AS [Plants];

推荐文章