如何用连接嵌套sql结果 - How to nest sql results with joins

How to nest sql results with joins

本文关键字：sql 结果嵌套连接何用 | 更新日期: 2023-09-27

我不得不去DB Tables

Macro Table:
ID | title | description
Micro Table:
ID | title | description | macro_id

macro_id在"Micro Table"中匹配"Macro Table"的ID。我有许多微型电脑对一个Maco电脑。

我正试图写一个sql语句，将给我一些结果像这样在PHP。

[
  {
    ID : 3, title : "some title", description : "some desc", micros : [ 
     {
       ID : some number,
       title : "some title",
       description : "some description", 
       macro_id : 3
     }
    ]
  }
]

一个对象数组，其中每个对象都来自宏表，然后在每个宏对象中，根据微表中的"macro_id"和宏表中的"ID"的匹配ID，与特定宏相匹配的"micro"对象数组

我尝试了各种连接

 SELECT * FROM macros JOIN micros ON macros.macro_id=micros.micro_id

但是这只是在一行中返回它，而不是嵌套它。

编辑:这就是我正在做的(它是在codeigniter)，这给了我我正在寻找的结果。这看起来效率很低

 $this->load->model('macros_model','',true);
    $this->load->model('micros_model', '', true);
    $all_macros = $this->macros_model->get_all_macros();
    $all_lessons = array();
    foreach($all_macros as $macro){
        $micros = $this->micros_model->get_all_micros_for_macro($macro['macro_id']);
        $macro['micros'] = $micros;
        array_push($all_lessons, $macro);
    }

get_all_macros和get_all_micros_for_macro中的Sql查询只是SELECT * FROM macros, and SELECT * FROM micro WHERE macro_id = $macro_id

下面的示例可能会有所帮助(但未经过测试).

$query = '
            SELECT 
                macroTable.ID as macroID,
                macroTable.Title as macroTitle,
                macroTable.Description as macroDescription,
                microTable.ID as microID,
                microTable.ID as microTitle,
                microTable.ID as microDescription
            FROM  
                macroTable
                INNER JOIN microTable ON macroTable.ID = microTable.macro_id
            ORDER BY
                macroID
        ';
$resultArray = executeQuery($query);
if ($resultArray){
   $nestedResults = array();
   $foreach ($resultArray as $row){
       $macroId = $row['macroID'];
       if (isset($nestedResults)){
           $nestedResults[$macroId]['micros'][] = array(
                                                          'microID'   =>   $row['microID'],
                                                          'microTitle'   =>   $row['microTitle'],
                                                          'microDescription'   =>   $row['microDescription'],
                                                       ); 
       } else {
           $nestedResults[$macroId] = array(
                                               'macroTitle'   =>   $row['macroTitle'],
                                               'macroDescription'   =>   $row['macroDescription'],  
                                               'micros'    =>  array()
                                           );
       }
   }
}