所以我有一個名為“log”的表，其中包含以下列，
id, endpoint ,response ,group
SAMPLE DATA.
1. endpoint1 ,{"last_name":"data here"} ,1234
2. endpoint2 ,{"first_name":"data here"} ,1234
3. endpoint3 ,{"dob":"12-21-2301"} ,1234
我想要實作的是撰寫一個查詢，該查詢可以生成按“組”列分組的記錄，最終輸出應該是這樣的。
{"last_name","data here","first_name":"data here","dob":"12-21-2301"}
對于每個鍵的每個記錄都是一列。謝謝
uj5u.com熱心網友回復：
WITH RECURSIVE
cte1 AS ( SELECT response,
`group`,
ROW_NUMBER() OVER (PARTITION BY `group`) rn
FROM log ),
cte2 AS ( SELECT response,
`group`,
rn
FROM cte1
WHERE rn = 1
UNION ALL
SELECT JSON_MERGE_PRESERVE(cte1.response, cte2.response),
cte1.`group`,
cte1.rn
FROM cte2
JOIN cte1 USING (`group`)
WHERE cte2.rn 1 = cte1.rn )
SELECT DISTINCT
FIRST_VALUE(response) OVER (PARTITION BY `group` ORDER BY rn DESC) responses,
`group`
FROM cte2;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=913b1923d7d5dbc7e42baeefb6e6ec86
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/418694.html
標籤：