php - 删除具有相同列值的行

我正在从邮政编码表中查询一堆城市。我只想用一个独特的“城市”来获得结果。 （很多城市都有多个邮递区。）如何做到这一点？

    $query = sprintf("SELECT `Zip`, `City`, `State`, `Lat`, `Long`,
             ( 3959 * acos( cos( radians('%s') ) * cos( radians( `Lat` ) ) *
             cos( radians( `Long` ) - radians('%s') ) +
             sin( radians('%s') ) * sin( radians( `Lat` ) ) ) ) AS distance
             FROM Zips HAVING distance < '%s' ORDER BY distance LIMIT 0 , 18",
      mysql_real_escape_string($lat),
      mysql_real_escape_string($lng),
      mysql_real_escape_string($lat),
      mysql_real_escape_string($radius));
    $result = mysql_query($query, $dbConn);

最佳答案

您在错误的地方使用了HAVING在这里需要WHERE并使用GROUP BY按城市分组结果

 $query = sprintf("SELECT `Zip`, `City`, `State`, `Lat`, `Long`, ( 3959 * acos( cos(
 radians('%s') ) * cos( radians( `Lat` ) ) * cos( radians( `Long` ) - radians('%s') ) + sin(
 radians('%s') ) * sin( radians( `Lat` ) ) ) ) AS distance FROM Zips WHERE distance < '%s'
 GROUP BY City ORDER BY distance LIMIT 0 , 18",

编辑

您也可以尝试

     $query = sprintf("SELECT `Zip`, `City`, `State`, `Lat`, `Long`, ( 3959 * acos( cos(
     radians('%s') ) * cos( radians( `Lat` ) ) * cos( radians( `Long` ) - radians('%s') ) + sin(
     radians('%s') ) * sin( radians( `Lat` ) ) ) ) AS distance FROM Zips HAVING distance < '%s'
     GROUP BY City  ORDER BY distance LIMIT 0 , 18";

要么

$query = sprintf("SELECT * FROM (SELECT `Zip`, `City`, `State`, `Lat`, `Long`, ( 3959 * acos( cos(
     radians('%s') ) * cos( radians( `Lat` ) ) * cos( radians( `Long` ) - radians('%s') ) + sin(
     radians('%s') ) * sin( radians( `Lat` ) ) ) ) AS distance FROM Zips HAVING distance < '%s'
     ORDER BY distance LIMIT 0 , 18 ) as result_set GROUP BY City";

关于php - 删除具有相同列值的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5590814/