如何從單個MySQL表中獲取由PHP中的多個列分組的多維陣列？-有解無憂

我有一個名為“guids”的表，如下所示：

id |survey_type |guid |guid_type |table_id |created_at |
2 |TYPE 1 |6C7251E3-2151-4754-A413-51899FAAF6C2 |question |2 |2022-03-20 16:14:09 |
3 |TYPE 1 |EF5AFA93-C74D-4920-A13A-17A9B43239CD |question |3 |2022-03-20 16:14:09 |
4 |TYPE 1 |5C059148-94BE-4225-B5C2-551A81B65F16 |question |4 |2022-03-20 16:14:09 |
5 |TYPE 1 |356B8A5C-1072-47A5-A508-D9BDCBA92CCC |answer |5 |2022-03-20 16:14:09 |
6 |TYPE 1 |E0CE4C26-7ABD-4162-9C8C-B4DD540AE268 |answer |6 |2022-03-20 16:14:09 |
7 |TYPE 1 |BFBC50FC-892D-43E9-A235-D76E0D0BEF29 |answer |7 |2022-03-20 16:14:09 |
8 |TYPE 2 |B9DCC5C1-CBFB-4589-98EF-4524F3958968 |survey |8 |2022-03-20 16:14:09 |
9 |TYPE 2 |C98FBFF9-6FE3-414E-BB14-08EDC8281E66 |survey |9 |2022-03-20 16:14:09 |
10 |TYPE 2 |8A780B6E-EAE0-47D6-9D05-F52B795AE617 |question |10 |2022-03-20 16:14:09 |
11 |TYPE 2 |E3818D30-BB69-4F03-B56D-B31691F8007E |question |11 |2022-03-20 16:14:09 |
12 |TYPE 2 |24C81BEF-BFCE-4964-AB01-F3579251313D |answer |12 |2022-03-20 16:14:09 |
13 |TYPE 3 |59381701-AFBC-48F8-AECE-DB3702EE2B15 |answer |13 |2022-03-20 16:14:09 |
14 |TYPE 3 |7F4AC694-74DC-4BEA-ACFB-D8F070769FEE |answer |14 |2022-03-20 16:14:09 |
15 |TYPE 3 |B5C405B9-BA7E-471A-87DD-B69D9757276F |survey |15 |2022-03-20 16:14:09 |

從那我想以這種格式獲取資料：

Array
(
    [survey_type] => Array
        (
            [guid_type] => Array
                (
                    [table_id] => guid
                    ...
                )
            ... // all guid types
        )
    ... // all survey types
)

基本上，我需要使用多個列來構建一個包含分組資料的嵌套陣列。我知道我可以使用這樣的 foreach 回圈來實作該結構：

// select existing guids
$this->db->select('*')
      ->from('guids');
$query = $this->db->get();
$existing_guids = $query->result_array();
$formated_guids = [];

foreach($existing_guids as $key => $guid_data) {
    $formated_guids[$guid_data['survey_type']][$guid_data['guid_type']][$guid_data['table_id']] = $guid_data['guid'];
}

但如果可能的話，我希望在資料庫層有一個更優化的解決方案，因為該表預計有數百萬行，并且回圈遍歷整個表似乎不是最優化的方法。

我玩過 GROUP_CONCAT mysql 命令一段時間，但未能產生所需的結構

SELECT `survey_type`, GROUP_CONCAT(`guid_type`) AS `guid_types` FROM guids GROUP BY `survey_type`

帶有模擬資料的期望結果：

Array
(
    [TYPE 1] => Array // survey type column
        (
            [question] => Array // guid_type column
                (
                    // table_id => guid
                    [1] => C8D21BD8-DA62-43C6-8E0D-0524D4F093B6
                    [2] => 6C7251E3-2151-4754-A413-51899FAAF6C2
                    ...
                )

            [survey] => Array
                (
                    [14] => B8B1F361-34A2-430E-9C0E-EB0C515B4E79
                    ...
                )

            [answer] => Array
                (
                    [2906] => 1A2F133E-0491-4117-AE2E-E3A823B66FBD...
                )

        ),
    [TYPE 2] => Array
        (
        ... // same structure as above
)

uj5u.com熱心網友回復：

首先，我建議您堅持使用您的代碼解決方案。運行一個簡單的 SQL 查詢，并處理結果，將它們整理成您想要的嵌套陣列結構。您已經進行了編碼，如果您需要更改它，它更容易除錯并且更容易在將來更改。

我已經實作了類似你描述的東西，在 SQL 查詢中創建嵌套結構。這非常困難，而且 SQL 查詢非常復雜，如果我們將來需要修改嵌套結構的內容，這將是一個維護問題。

我在 SQL 查詢中使用的解決方案是使用多個級別的派生表子查詢，并使用 JSON 函式在每個級別生成聚合 JSON 結果。這需要使用 MySQL 5.7 或更高版本，因為這些 JSON 函式在早期版本的 MySQL 中沒有實作。

演示測驗資料：

create table if not exists mytable ( id int primary key, survey_type varchar(20), guid char(36), guid_type varchar(20), table_id  int, created_at datetime );
insert into mytable values 
(2 ,'TYPE 1','6C7251E3-2151-4754-A413-51899FAAF6C2','question','2','2022-03-20 16:14:09'), 
(3 ,'TYPE 1','EF5AFA93-C74D-4920-A13A-17A9B43239CD','question','3','2022-03-20 16:14:09'), 
(4 ,'TYPE 1','5C059148-94BE-4225-B5C2-551A81B65F16','question','4','2022-03-20 16:14:09'), 
(5 ,'TYPE 1','356B8A5C-1072-47A5-A508-D9BDCBA92CCC','answer','5','2022-03-20 16:14:09'), 
(6 ,'TYPE 1','E0CE4C26-7ABD-4162-9C8C-B4DD540AE268','answer','6','2022-03-20 16:14:09'), 
(7 ,'TYPE 1','BFBC50FC-892D-43E9-A235-D76E0D0BEF29','answer','7','2022-03-20 16:14:09'), 
(8 ,'TYPE 2','B9DCC5C1-CBFB-4589-98EF-4524F3958968','survey','8','2022-03-20 16:14:09'), 
(9 ,'TYPE 2','C98FBFF9-6FE3-414E-BB14-08EDC8281E66','survey','9','2022-03-20 16:14:09'), 
(10 ,'TYPE 2','8A780B6E-EAE0-47D6-9D05-F52B795AE617','question','10','2022-03-20 16:14:09'), 
(11 ,'TYPE 2','E3818D30-BB69-4F03-B56D-B31691F8007E','question','11','2022-03-20 16:14:09'), 
(12 ,'TYPE 2','24C81BEF-BFCE-4964-AB01-F3579251313D','answer','12','2022-03-20 16:14:09'), 
(13 ,'TYPE 3','59381701-AFBC-48F8-AECE-DB3702EE2B15','answer','13','2022-03-20 16:14:09'), 
(14 ,'TYPE 3','7F4AC694-74DC-4BEA-ACFB-D8F070769FEE','answer','14','2022-03-20 16:14:09'), 
(15 ,'TYPE 3','B5C405B9-BA7E-471A-87DD-B69D9757276F','survey','15','2022-03-20 16:14:09');

示例查詢：

select json_pretty(json_objectagg(survey_type, g)) as j
from (
  select survey_type, json_objectagg(guid_type, t) as g
  from (
    select survey_type, guid_type, json_objectagg(table_id, guid) as t
    from mytable
    group by survey_type, guid_type
  ) as t
  group by survey_type
) as g;

輸出：

{
  "TYPE 1": {
    "answer": {
      "5": "356B8A5C-1072-47A5-A508-D9BDCBA92CCC",
      "6": "E0CE4C26-7ABD-4162-9C8C-B4DD540AE268",
      "7": "BFBC50FC-892D-43E9-A235-D76E0D0BEF29"
    },
    "question": {
      "2": "6C7251E3-2151-4754-A413-51899FAAF6C2",
      "3": "EF5AFA93-C74D-4920-A13A-17A9B43239CD",
      "4": "5C059148-94BE-4225-B5C2-551A81B65F16"
    }
  },
  "TYPE 2": {
    "answer": {
      "12": "24C81BEF-BFCE-4964-AB01-F3579251313D"
    },
    "survey": {
      "8": "B9DCC5C1-CBFB-4589-98EF-4524F3958968",
      "9": "C98FBFF9-6FE3-414E-BB14-08EDC8281E66"
    },
    "question": {
      "10": "8A780B6E-EAE0-47D6-9D05-F52B795AE617",
      "11": "E3818D30-BB69-4F03-B56D-B31691F8007E"
    }
  },
  "TYPE 3": {
    "answer": {
      "13": "59381701-AFBC-48F8-AECE-DB3702EE2B15",
      "14": "7F4AC694-74DC-4BEA-ACFB-D8F070769FEE"
    },
    "survey": {
      "15": "B5C405B9-BA7E-471A-87DD-B69D9757276F"
    }
  }
}

將該結果提取到您的客戶端應用程式中。這是一個長字串，所以我希望你的資料不超過 MySQL 的max_allowed_packet長度。

使用json_decode()將字串轉換為所需的嵌套陣列。

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/445960.html

標籤：php mysql sql 表现代码点火器

上一篇：在JS中訪問和更改父物件的屬性

下一篇：查詢where_in逗號分隔串列