mysql - 压缩 Mysql 查询

Question

我希望将这三个 mySql 查询压缩为一个查询

$qry = "SELECT DISTINCT authentication_id FROM gps_trak WHERE DATE(location_timestamp) = DATE(NOW())";

我使用上述查询进行身份验证，然后循环遍历结果以获取该人今天的每个纬度和经度，并获取他/她的名字...

$qry ="SELECT latitude, longitude FROM gps_trak WHERE authentication_id = ".$authID." ORDER BY location_timestamp DESC LIMIT 8";

$qry = "SELECT CONCAT('forename ', 'surname') AS name FROM authentication WHERE authentication_id = ".$authID;

你能把它写成一个单个查询，这样我就可以得到

$resultset=array("id"=>$authID,"name"=>$name,"locs"=>$locArray);

*编辑:* gps_trak 表中的单个 authkey 可以有多个纬度和经度值...

Answer 1

我没有安装 MySql，但查询是这样的:

SELECT a.authentication_id, CONCAT(a.forname, a.surname) AS name, g.latitude, g.longitude, g.location_timestamp
FROM authentication a
INNER JOIN gps_trak g on a.authentication_id = g.authentication_id
WHERE DATE(g.location_timestamp) = DATE(NOW()) 
GROUP BY a.authentication_id, a.forname, a.surname, g.latitude, g.longitude, g.location_timestamp

您不能在此解决方案中使用 limit，您必须在代码中对结果进行分组。也许是这样的PHP group by并按authentication_id分组。

编辑

更新了 SQL。在 PHP 中执行类似的操作(有关详细信息，请查看上面的链接):

<?php
$result = mysql_unbuffered_query($qry);
$set = array();
while ($record = mysql_fetch_object($result)) {
  $set[$record->authentication_id][] = $record;
}
mysql_free_result($result);
foreach ($set as $authentication_id => $records) {
  /* do something with your name and auth key*/
  foreach ($records as $record) {
    /* do something with the long and lat and you can limit it to 8 results sorting with rsort() for descending*/
  }
}
?>