如何在SQL查询中进行2个分组，并导出为JSON

Question

下面是我的mySQL商店的csv形式的示例。

trek_id, number, name, value, units, userID, deviceID, lat        , lng
--------------------------------------------------------------------------------
     88,      4, Hum ,   720, PPB  ,     96,         1, 40.0215268, -105.2177324
     88,      4, PM10,   720, PPB  ,     96,         1, 40.0215268, -105.2177324
     88,      6, Pres,   730, PPB  ,     96,         1, 40.0215299, -105.2177096
     88,      6, PM10,   730, PPB  ,     96,         1, 40.0215299, -105.2177096

因此，一个trek_id有多个number值，每个数字包含多个测量值。我很想知道如何查询它，这样我就可以最终插入到如下所示的json对象中：

{  
"88":{  
   "4":{  
      "lat":"40.0215268",
      "lng":"-105.2177324",
      "userID":96,
      "deviceID":"1",
      "measurements":[
        ["Hum",
         "PPB",  
           720
        ],
        ["PM10",
         "PPB",
         720
        ]
      ]
    },
   "6":{  
      "lat":"40.0215299",
      "lng":"-105.2177096",
      "userID":96,
      "deviceID":"1",
      "measurements":[
        ["Pres",
         "PPB",  
           730
        ],
        ["PM10",
         "PPB",
         720
        ]
      ]
    }
  }
}

所以基本上我需要先在trek_id上分组，然后再在number上分组。

Answer 1

好的，首先要做的是: SQL结果是平面表格。它们没有子组或子数组。所以你需要处理显示代码中的所有子分组。

其次:您现有的数据已经非常适合显示代码(比方说PHP)来创建多维数组来转换为JSON。

-提示PSEUDO_CODE

<?php
// assume  $results contains your data AND SORTED BY trek_id, number, name

$cur_trek   = null;
$cur_number = null;
$json = array();

foreach ($results as $res) {
   if ($cur_trek != $res['trek_id']) {
      $cur_trek   = $res['trek_id'] ;
      $json[$cur_trek] = array();
      $cur_number = null;
   }
   if ($cur_number != $res['number']) {
       $cur_number = $res['number'];
       $json[$cur_trek][$cur_number] = 
         array(
           'lat'          => $res['lat'],
           'lng'          => $res['lng'],
           'userID'       => $res['userID'],
           'deviceID'     => $res['deviceID'],
           'measurements' => array();
         );
   }
   $json[$cur_trek][$cur_number]['measurements'][] = 
     array($res['name'], $res['units'], $res['value']);
}

$json = json_encode($json);