sql-server - 如何根据匹配ID合并/连接单行中两个表的结果

Question

我有两个 SQL Server 表commonformsschema和ApplicationRoles, 以及两个表中的一个公共SchemaId列。

在一个schemaId列中是唯一的，而在另一个表中该schemaid列具有多次。现在使用连接，我必须根据schemaid列将两个表合并到单行中。

这是我正在尝试的查询

WITH fschema2 AS
(
    SELECT * 
    FROM
        (SELECT 
             fs1.SchemaId, fs1.[Schema],
             ROW_NUMBER() OVER (PARTITION BY fs1.schemaName
                                ORDER BY CASE WHEN fs1.tenant = 'ALL' THEN 2 ELSE 1 END, fs1.tenant) AS seqnum 
         FROM
             commonformsschema fs1 
         WHERE 
             fs1.Tenant IN ('constructiontest', 'All')) t3
    WHERE 
        seqnum = 1
)
SELECT 
    json_modify(fs2.[Schema],
    ISNULL(N'$.roles[' + j.[key] COLLATE Latin1_General_BIN2 + N']', N'append $.roles'), 
    json_query((SELECT 
                    ar1.rolename AS [role],
                    ar1.[create] AS [permissions.create],
                    ar1.[read]   AS [permissions.read],
                    ar1.[update] AS [permissions.update],
                    ar1.[delete] AS [permissions.delete]
                 FOR JSON PATH, without_array_wrapper))
     ) AS [Schema] FROM fschema2 fs2
JOIN
    ApplicationRoles ar1 ON ar1.schemaId = fs2.schemaid
OUTER APPLY 
    (SELECT TOP (1) j.[key]
     FROM OPENJSON(fs2.[Schema], '$.roles') j
     WHERE JSON_VALUE(j.value, '$.role') = ar1.rolename) j;

有关输入和表详细信息的更多详细信息，请参阅db fiddler。

当前结果（3 行）：

架构

{
  "roles": [
     {
      "role": "Read",
      "permissions": {
        "read": true,
        "create": false,
        "update": false,
        "delete": false
      }
    }
  ,{"role":"Construction Manager Admin","permissions":{"create":false,"read":false,"update":false,"delete":false}}]
}
{
  "roles": [
     {
      "role": "Read",
      "permissions": {
        "read": true,
        "create": false,
        "update": false,
        "delete": false
      }
    }
  ,{"role":"Project Manager Admin","permissions":{"create":true,"read":true,"update":true,"delete":true}}]
}
{
  "roles": [
     {"role":"Read","permissions":{"create":true,"read":false,"update":false,"delete":false}}
  ]
}

预期结果（1 行）：

架构

{
  "roles": [
     {"role":"Read","permissions":{"create":true,"read":false,"update":false,"delete":false}},
     {"role":"Project Manager Admin","permissions":{"create":true,"read":true,"update":true,"delete":true}},
     {"role":"Construction Manager Admin","permissions":{"create":false,"read":false,"update":false,"delete":false}}
  ]
}

有关任何其他详细信息，请告诉我。

提前致谢。

标签： sql-servertsql