如何將多個 ROW 分組為 ARRAY

這是我目前的查詢：

SELECT innings_no,
Player_Name,
sum(NoBalls)                                                    as NB
   FROM (
          SELECT A.innings_no,
                 A.bowler,
                 A.Runs_In_Over + ifnull(B.Extra_runs, 0) = 0 as Maiden,
                 A.Balls - ifnull(B.NoBalls, 0)               as Balls,
                 A.Runs_In_Over + ifnull(B.Extra_runs, 0)     as Runs,
                 ifnull(C.Wicket, 0)                          as Wickets,
                 Zero,
                 Four,
                 Six,
                 ifnull(Wides, 0)                             as Wides,
                 ifnull(NoBalls, 0)                           as Noballs
          FROM (SELECT over_id,
                       ABBB.innings_no,
                       ABBB.bowler,
                       count(ABBB.ball_id)                 as Balls,
                       Sum(ABB.Runs_Scored)               As 'Runs_In_Over',
                       SUM(if(ABB.runs_scored = 0, 1, 0)) as Zero,
                       SUM(if(ABB.runs_scored = 4, 1, 0)) as Four,
                       SUM(if(ABB.runs_scored = 6, 1, 0)) as Six
                FROM `database`.ball_by_ball ABBB
                       INNER JOIN `database`.batsman_scored ABB
                                  using (match_id, over_id, ball_id, innings_no)
                where match_id = 981018
                GROUP BY over_id, innings_no, bowler
               ) as A
                 LEFT JOIN
               (SELECT BBBB.over_id,
                       BBBB.innings_no,
                       BBBB.bowler,
                       SUM(if(BER.extra_type_id = 2, 1, 0)) as Wides,
                       SUM(if(BER.extra_type_id = 4, 1, 0)) as NoBalls,
                       Sum(BER.Extra_Runs)                  As 'Extra_runs'
                FROM `database`.ball_by_ball BBBB
                       INNER JOIN `database`.extra_runs BER
                                  using (match_id, over_id, ball_id, innings_no)
                WHERE match_id = 981018
                  and BER.Extra_Type_Id IN (2, 4)
                GROUP BY over_id, innings_no, bowler
               ) as B
               using (over_id, innings_no, bowler)
                 LEFT JOIN
               (SELECT over_id,
                       CBBB.innings_no,
                       CBBB.bowler,
                       count(CBBB.bowler) as Wicket
                FROM `database`.ball_by_ball CBBB
                       INNER JOIN `database`.wicket_taken CWT
                                  using (match_id, over_id, ball_id, innings_no)
                WHERE match_id = 981018
                  and CWT.kind_out in (1, 2, 4, 6, 7, 8)
                GROUP BY over_id, innings_no, bowler
               ) as C
               using (over_id, innings_no, bowler)
          order by innings_no, over_id
          limit 1000
        ) AS DATA
          INNER JOIN `database`.Player C
                     ON DATA.bowler = C.Player_Id
   GROUP BY innings_no, Player_Name;

這是輸出：

+------------+---------------+------+
| innings_no | Player_Name   | NB   |
+------------+---------------+------+
|          1 | S Aravind     |    0 |
|          1 | Iqbal Abdulla |    0 |
|          1 | SR Watson     |    0 |
|          1 | CJ Jordan     |    0 |
|          1 | YS Chahal     |    0 |
|          1 | STR Binny     |    0 |
|          2 | P Kumar       |    0 |
|          2 | DS Kulkarni   |    0 |
|          2 | RA Jadeja     |    0 |
|          2 | SB Jakati     |    1 |
|          2 | DJ Bravo      |    0 |
|          2 | DR Smith      |    0 |
+------------+---------------+------+

我想得到如下輸出：

+------------+--------------------------------+------------+
| innings_no |           Player_Name          |     NB     |
+------------+--------------------------------+------------+
|          1 | ["S Aravind", .. ,"STR Binny"] | [0, .. ,0] |
|          2 | ["P Kumar", .. ,"DR Smith"]    | [0, .. ,0] |
+------------+--------------------------------+------------+

在上面的輸出中，我想將我的結果分組innings_no並將這兩行轉換為一個數組。我嘗試了JSON_ARRAYAGG()功能，Player_Name 但輸出不是我想要的，它也不起作用sum(NoBalls)。

我該如何解決這個問題？

WITH cte AS (`your query text except last semicolon`)
SELECT innings_no,
      JSON_ARRAYAGG(Player_Name) Player_Names,
      JSON_ARRAYAGG(NB) NBs
FROM cte
GROUP BY innings_no;

如果您需要根據 Player_Name 和 NB 的嚴格位置，請使用

WITH cte AS (`your query text except last semicolon`)
SELECT DISTINCT innings_no,
               JSON_ARRAYAGG(Player_Name) OVER win Player_Names,
               JSON_ARRAYAGG(NB) OVER win NBs
FROM cte
WINDOW win AS (PARTITION BY innings_no ORDER BY Player_Name);

您GROUP BY只需要innings_no否則每個唯一的innings_no，player_name都是唯一的條目。

要獲取聚合的 player_names：

CONCAT('[', GROUP_CONCAT(CONCAT('"', player_name, '"'), ']') as Player_Names

和NB

CONCAT('[', GROUP_CONCAT(NoBalls), ']') as NBs

引用自：https://dba.stackexchange.com/questions/232784