将Server查询重写为postgresql并用string_agg()替换with ()时出错

Question

我正在将一个应用程序从Server迁移到Postgresql，并且必须重写一些定期运行的查询。我在其中一个方面遇到了困难，因为我必须用stuff()和XML Path替换string_agg()。通常我可以这样做，但是在本例中，stuff()是一个子查询，所以在运行这个查询时，我会收到一个“由子查询作为表达式返回的多行”错误。我在SQL和Postgresqlto方面没有足够的经验来解决这个问题。

我陷入困境的SQL Server部分：

SELECT P.pkey + '-' + CAST(JI.issuenum AS VARCHAR(18)) [CASE NUMBER]
, IT.pname [ISSUE TYPE (PREVIOUSLY CASE REASON)]
, JI.CREATED [OPENED DATE]
, CRE.display_name [CREATOR]
, DEP.BIG_DATA [DEPARTMENT]
, REG.BIG_DATA [OFFICE]
, JI.RESOLUTIONDATE [CLOSED DATE]
, STUFF((     SELECT CAST(', ' + COM.cname AS VARCHAR(MAX))
                     FROM nodeassociation NAC
                     INNER JOIN component COM ON NAC.SINK_NODE_ID = COM.ID
                     WHERE NAC.SOURCE_NODE_ID = JI.ID
                     ORDER BY COM.cname
                     FOR XML PATH('')), 1, 2, '') [COMPONENT(S)]
, RCS.customvalue [RESOLUTION CODE (SERVICE)]
, ISNULL(RCI1.customvalue + ' - ', '') + RCI2.customvalue [RESOLTUION CODE (INCIDENT)]
, ISNULL(RCMT1.customvalue + ' - ', '') + ISNULL(RCMT2.customvalue + ' - ','') + ISNULL(RCMT3.customvalue,'') [RESOLTUION CODE (3-tier)]

我失败的postgres重写，返回错误：

由用作表达式的子查询返回的多行

SELECT P.pkey || '-' || CAST(JI.issuenum AS VARCHAR(18)) AS "CASE NUMBER"
, IT.pname "ISSUE TYPE (PREVIOUSLY CASE REASON)"
, JI.CREATED "OPENED DATE"
, CRE.display_name "CREATOR"
, JI.RESOLUTIONDATE "CLOSED DATE"
, string_agg((SELECT COM.cname 
                     FROM nodeassociation NAC
                     INNER JOIN component COM ON NAC.SINK_NODE_ID = COM.ID
                     WHERE NAC.SOURCE_NODE_ID = JI.ID
                     ORDER BY COM.cname, p.pkey, JI.issuenum, IT.pname, JI.CREATED, CRE.display_name, JI.RESOLUTIONDATE, RCS.customvalue, RCI1.customvalue, RCI2.customvalue, RCMT1.customvalue, RCMT2.customvalue, RCMT3.customvalue, DEP."BIG_DATA", REG."BIG_DATA" 
                     ), '') AS COMPONENTS
, RCS.customvalue RESOLUTION_CODE_SERVICE
, coalesce(RCI1.customvalue || ' - ', '') || RCI2.customvalue "RESOLTUION-CODE-INCIDENT"
, coalesce(RCMT1.customvalue || ' - ', '') || coalesce(RCMT2.customvalue || ' - ','') || coalesce(RCMT3.customvalue,'') "RESOLTUION-CODE 3-tier"
, DEP."BIG_DATA" "DEPARTMENT"
, REG."BIG_DATA" "OFFICE"

我使用了这个关于用old post替换stuff()的string_agg()，但是它没有包含子查询。

Answer 1

试着替换这个：

string_agg((SELECT COM.cname 
                     FROM nodeassociation NAC
                     INNER JOIN component COM ON NAC.SINK_NODE_ID = COM.ID
                     WHERE NAC.SOURCE_NODE_ID = JI.ID
                     ORDER BY COM.cname, p.pkey, JI.issuenum, IT.pname, JI.CREATED, CRE.display_name, JI.RESOLUTIONDATE, RCS.customvalue, RCI1.customvalue, RCI2.customvalue, RCMT1.customvalue, RCMT2.customvalue, RCMT3.customvalue, DEP."BIG_DATA", REG."BIG_DATA" 
                     ), '') AS COMPONENTS

在这方面：

(SELECT string_agg(COM.cname,'')
                     FROM nodeassociation NAC
                     INNER JOIN component COM ON NAC.SINK_NODE_ID = COM.ID
                     WHERE NAC.SOURCE_NODE_ID = JI.ID
                     ORDER BY COM.cname, p.pkey, JI.issuenum, IT.pname, JI.CREATED, CRE.display_name, JI.RESOLUTIONDATE, RCS.customvalue, RCI1.customvalue, RCI2.customvalue, RCMT1.customvalue, RCMT2.customvalue, RCMT3.customvalue, DEP."BIG_DATA", REG."BIG_DATA" 
                     ) AS COMPONENTS