优化mysql组通过 - optimization mysql group by

optimization mysql group by

本文关键字：mysql 优化 | 更新日期: 2023-09-27

我有一个类似的表

表名：products

+----+-------------+-----------+
| id |    name     |   type    |
|  1 |    apple    |   fruit   | 
|  2 |    banana   |   fruit   | 
|  3 |    tomato   | vegetable | 
|  4 |  egg plant  | vegetable |  
|  5 |    carrot   | vegetable | 
|  6 | Minced Steak|   meat    | 
|  7 |  Pork ribs  |   meat    | 
+----+-------------+-----------+

我想要输出html像：

<ul>
  <li>apple</li>
  <li>banana</li>
</ul>
<ul>
  <li>tomato</li>
  <li>egg plant</li>
  <li>carrot</li>
</ul>
<ul>
  <li>Minced Steak</li>
  <li>Pork ribs</li>
</ul>

我不知道是否有一个简单的mysql查询。我的复杂mysql查询如下：

$result = mysql_query("SELECT * FROM products group by type ORDER BY type");
while ($data = mysql_fetch_array($result)){
    echo '<ul>';    
        $querys = "select * FROM products WHERE type = ".$data['type']." ";
    $results = mysql_query($querys);
    while($row = mysql_fetch_array($results)){
        echo '<li>'.$row['name'].'</li>';
    }
    echo '</ul>';
}

显然这是低效的，如何改进？

您只需要第一个查询。

$result = mysql_query("SELECT type, name FROM products ORDER BY type");
$grouped_data = array();
while ($data = mysql_fetch_array($result)){
    $grouped_data[$data['type']][] = $data['name'];
}

现在$grouped_data给出了您想要的结果数组。

输出也很简单：

foreach ($grouped_data as $data) {
    echo '<ul><li>' . join('</li><li>', $data) . '</li></ul>';
}

如果您想直接编写HTML，请使用组更改循环：

$oldgroup='__invalid__';
$needsendul=false;
$result = mysql_query("SELECT * FROM products ORDER BY type");
while ($data = mysql_fetch_array($result)) {
    //Group change?
    $newgroup=$data['type'];
    if ($newgroup!=$oldgroup) {
        if ($needsendul) echo '</ul>';
        echo '<ul>';
        $needsendul=true;
        $oldgroup=$newgroup;
    }
    echo '<li>'.$row['name'].'</li>';
}
//Final closing tag
echo '</ul>';

在循环中运行查询是不好的。当您已经拥有所需的所有数据时，在循环中运行查询会更糟。如果调整循环以在每次迭代中跟踪类型，并在类型更改时采取适当的操作，则不需要重新获取循环中的数据。

$type = '';
while ($data = mysql_fetch_assoc ($result))
{
    if ($type != $data ['type'])
    {
        $type = $data ['type'];
        // Take whatever action you need to take when the type changes here
    }
    // Do the usual output logic here
}

首先获取数据并制作如下2D数组：

   $result = mysql_query("SELECT id,type, name FROM products ORDER BY type");
    $data = array();
    while ($row = mysql_fetch_array($result)){
        $data[$row['type']][$row['id']] = $row['name'];
    }

对于在<ul>中打印，<li>执行如下操作：

foreach ($data as $types=>$namesarray) {
echo '<ul>';
    foreach($namesarray as $Key=>$Val){
        echo '<li>'.$namesarray[$types][$Key]."</li>";
        }
echo '</ul>';
}

使用group-by和group_concat

$sql = "SELECT type, GROUP_CONCAT(name) AS names FROM product GROUP BY type";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)){
  echo "<ul>";
  $exploded = explode(',', $row['names']);
  foreach($exploded as $name){
    echo "<li>$name</li>";
  }
  echo "</ul>";
}