問題描述
我想根據另一個表中的幾個字段動態提取數據行，并在將其作為單行加入時將其匯總為 JSON.
I'd like to dynamically pull rows of data based on a few fields from another table and summarize it as JSON when joining it in as a single row.
這是一個小例子來說明.
Here's a small example to illustrate.
[測試].[dbo].[tableA]
| Col1 | Col2 |
|---|---|
| 1 | 我 |
| 2 | ii |
| 3 | iii |
[測試].[dbo].[tableB]
| A_id | B_Col1 | B_Col2 |
|---|---|---|
| 1 | b11 | b12 |
| 1 | b111 | b112 |
| 2 | b21 | b22 |
| 2 | b22 | b222 |
查詢:
SELECT * FROM [Test].[dbo].[tableA] as A
CROSS APPLY (
SELECT (
SELECT * FROM [Test].[dbo].[tableB] as B
WHERE B.A_id = A.Col1
FOR JSON PATH
) as B_JSON
) as CA
結果(在 SQL Server 中符合預期)
Result (as expected in SQL Server)
| Col1 | Col2 | B_JSON |
|---|---|---|
| 1 | 我 | [{A_id":1，B_Col1":b11"，B_Col2":b12"}，{A_id":1，B_Col1":b111",B_Col2":b112"}] |
| 2 | ii | [{A_id":2，B_Col1":b21"，B_Col2":b22"}，{A_id":2，B_Col1":b22",B_Col2":b222"}] |
| 3 | iii | NULL |
Azure Synapse 無服務器 SQL 池中的結果:
該查詢引用了分布式中不支持的對象處理方式.
The query references an object that is not supported in distributed processing mode.
問題是，它不喜歡 FOR JSON 結果周圍的 SELECT，但我們需要它來分配一個列名，以便交叉應用工作.
Trouble is, it doesn't like the SELECT around the FOR JSON result, but we need that to assign a column name such that the Cross Apply works.
問題是這樣的；在這種情況下實現這一目標的最佳方法是什么?
Question is thus; what is the best way to achieve this within this context?
推薦答案
我無法在您的環境中對此進行測試，因此這可能不起作用...您可以嘗試以下方法之一:
I cannot test this in your environment, so this might not work... You can try one of these:
DECLARE @tblA TABLE(Col1 INT, Col2 VARCHAR(10));
INSERT INTO @tblA(Col1,Col2) VALUES
(1,'i')
,(2,'ii')
,(3,'iii');
DECLARE @tblB TABLE(A_id INT,B_Col1 VARCHAR(10),B_Col2 VARCHAR(10));
INSERT INTO @tblB(A_id,B_Col1,B_Col2) VALUES
(1,'b11','b12')
,(1,'b111','b112')
,(2,'b21','b22')
,(2,'b22','b222');
--在 CA 名稱后面傳遞列名稱(避免嵌套的 SELECT)
--Pass the column's name behind the CA's name (avoids the nested SELECT)
SELECT * FROM @tblA as A
CROSS APPLY (
SELECT * FROM @tblB as B
WHERE B.A_id = A.Col1
FOR JSON PATH
) CA(B_JSON);
--使用標量子選擇完全避免 CA
--Avoid the CA totally by using a scalar sub-select
SELECT A.Col1
,A.Col2
,(
SELECT * FROM @tblB as B
WHERE B.A_id = A.Col1
FOR JSON PATH
) AS B_JSON
FROM @tblA as A;
這篇關于Azure Synapse 如何交叉應用 JSON 路徑的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！