在对两个表执行 JOIN 的查询中使用 MySQL 中的 GROUP BY 时出现问题

Question

我有两个 MySQL 表，$database1 和 $database2。两者都有一个名为 ID 的字段。我使用 GET 将城镇名称传递给文件（即，它位于保存此代码的 PHP 文件的 URL 中）。

我可以运行这个查询...

$PlaceName = $_GET['townName'];
$PlaceName = mysql_real_escape_string($PlaceName);

$sql="SELECT * from $database1 LEFT JOIN $database2 on $database1.ID = $database2.ID WHERE PlaceName='$PlaceName'";
$query = mysql_query($sql);

echo '<h1>People who are searching for '.$PlaceName.':</h1>';
echo '<ul>';
while ($row = mysql_fetch_array($query)) { 
   echo "<li>ID #",$row['ID'],": ",$row['MemberPersonalName']," ",$row['MemberSurname']," -- searching for ",$row['SurnameBeingSearched'],"</li>";
   }
echo '</ul>';

...它有效并且一切都很好。现在输出看起来像这样...

正在搜索 Hogwarts 的人：

• ID #137：Hermione Granger - 搜索 Stern
• ID #137：Hermione Granger - 搜索 Engelberg
• ID ＃503：哈利·波特 - 搜索克雷德勒
• ID #549：罗恩·韦斯莱 - 搜索克雷德勒
• ID #1062：德拉科·马尔福 - 搜索英格堡
• ID #1155：金妮·韦斯莱 - 搜索克雷德勒
• ID #1155：金妮·韦斯莱 - - 搜索 Streisand

但输出需要调整，并且我在编写 SQL 查询语句来反映更改时遇到了困难。我真正想要的是输出看起来像这样...

正在搜索霍格沃茨的人：

• 赫敏·格兰杰（id #137）和德拉科·马尔福（id #137）正在搜索英格堡1062)
• 哈利·波特 (id #503)、罗恩·韦斯莱 (id #549) 和金妮·韦斯莱 (id #1155)
• 正在搜寻克雷德勒 (Kreindler) 赫敏·格兰杰 (id #137)
• 正在搜寻斯特恩 (Stern) 金妮·韦斯莱 (Ginny Weasley) 正在搜寻史翠珊(id #1155)

换句话说，我需要通过“SurnameBeingSearched”字段将输出分组在一起，我需要以“X、Y 和 Z”输出格式列出进行搜索的人员的姓名（其中知道在哪里添加逗号（如有必要，具体取决于结果的数量），并且我需要按“SurnameBeingSearched”字段对结果进行排序。

帮助？谢谢！

Answer 1

您需要列出名称，以便这不是聚合（在 SQL 意义上）问题。保留您当前的查询。您将必须在代码中进行分组。

所以像这样：

$rows = array();
$last = '';
while ($row = mysql_fetch_array($query)) {
  $surname = $row['SurnameBeingSearched'];
  $id = $row['ID'];
  $name = $row['MemberPersonalName'];
  if ($last != $surname) {
    $last = $surname;
    $rows[] = array();
  }
  $rows[count($rows)-1][$id] = $name;
}
foreach ($rows as $row) {
  // now display each group of names
}

Answer 2

您也许还可以使用 MySQL GROUP_CONCAT() 函数。

它看起来像这样...

SELECT places_tbl.name, GROUP_CONCAT(people_tbl.name) 
FROM places_tbl 
LEFT JOIN people_tbl ON (places_tbl.id = people_tbl.id) 
GROUP BY places_tbl.id

默认情况下，GROUP_CONCAT() 返回以逗号分隔的值。您可以根据需要将它们拆分以获取格式或使用 SEPARATOR 关键字。 GROUP_CONCAT（字段名SEPARATOR '-'）

Answer 3

$PlaceName = $_GET['townName'];
$PlaceName = mysql_real_escape_string($PlaceName);

// note - added order to the query
$sql="SELECT * from $database1 LEFT JOIN $database2 on $database1.ID = $database2.ID WHERE PlaceName='$PlaceName' 
      ORDER BY SurnameBeingSearched, MemberSurname, MemberPersonalName";
$query = mysql_query($sql);

echo '<h1>People who are searching for '.$PlaceName.':</h1>';
echo '<ul>';
  $cntr = mysql_num_rows($query);
  if ($cntr > 0) {
    $i = 0;
    $srchd = mysql_result($query, $i, 'SurnameBeingSearched');     
    $mbr = mysql_result($query, $i, 'MemberPersonalName');
    $mbr = $mbr . " " . mysql_result($query, $i, 'MemberSurname');     
    $mbr = $mbr . " (id #" . mysql_result($query, $i, 'ID') . ")";      
    $lin = $srchd . " is being searched by " . $mbr;
    $prev = $srchd;
    if ($cntr == 1) { 
      echo "<li>" . $lin . "</li>";            
    } else {
      for ($i = 1; $i< $cntr; $i++) {        
        $srchd = mysql_result($query, $i, 'SurnameBeingSearched');     
        $mbr = mysql_result($query, $i, 'MemberPersonalName');
        $mbr = $mbr . " " . mysql_result($query, $i, 'MemberSurname');     
        $mbr = $mbr . " (id #" . mysql_result($query, $i, 'ID') . ")";      
        if ($srchd == $prev) { // common search
          $j = $i + 1;  
          if ($j < $cntr) { // still have data
            $nxt = mysql_result($query, $j, 'SurnameBeingSearched');     
            if ($prev == $nxt) {  // another one coming -- use the comma
              $lin = $lin . ", " . $mbr;  
            } else {
              $lin = $lin . ", and " . $mbr;  // last member add the 'and' - line is done
              echo "<li>" . $lin . "</li>";
            }           
            $prev = $srchd; 
          } else { // ran out of data - need to finish the line
            $lin = $lin . ", and " . $mbr;  // last member add the 'and' - line is done
            echo "<li>" . $lin . "</li>";
        } else { // new search - need to print this line and start a new one
          echo "<li>" . $lin . "</li>";            
          $lin = $srchd . " is being searched by " . $mbr;
          $prev = $srchd;
        }  // test searched = previous
      }  // next i
    }  // only one row
  }  // cntr > 0 
echo '</ul>';

/* note: this is not tested 
   I would recommend using table1 and table2 instead of database1 and database2
   or better give the tables meaningful names
   I would use active voice instead of passive voice 
*/