mysql - 对连接查询结果进行分组

Question

我正在尝试编写一个查询来显示所有订单及其选项。到目前为止已经完成，但是当我尝试按订单 ID 对它们进行分组时，它只是为每个选项打印一个单独的行，而不是订单 ID 及其下的所有选项。

我错过了一些东西，但不知道它是什么:)

这是我所拥有的

$sql = 'SELECT *
        FROM `order` RIGHT JOIN `order_option`
        on order.order_id=order_option.order_id
        where order_status_id = 2 
        group by order_option.order_option_id';

$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    print "ID:{$row['order_id']}  <br> ".
         "OPTION NAME: {$row['name']} <br> ".
         "VALUE: {$row['value']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);

?>

**UPDATE 1**

这就是查询现在的工作方式

SELECT 
  o.order_id, 
  o.name, 
  o.value, 
  GROUP_CONCAT(o.order_id, o.name, o.value SEPARATOR ',') AS Options 
FROM `order_option` AS o 
LEFT JOIN `order` AS oo on o.order_id = oo.order_id 
where oo.order_status_id = 2 
group by o.order_id

Answer 1

您应该改为GROUP BY order_id，并使用GROUP_CONCAT来获取同一行中订单的所有选项:

SELECT 
  o.order_id,
  GROUP_CONCAT(oo.order_option_id SEPARATOR ',') AS Options
FROM `order` AS o
RIGHT JOIN `order_option` AS oo on o.order_id = oo.order_id
where o.order_status_id = 2 
group by o.order_id;

SQL Fiddle Demo

如果 GROUP_CONCAT 不是您要查找的内容，那么您必须使用其他聚合函数，但不要包含 GROUP BY 中没有的任何列SELECT 子句中没有聚合函数的子句。否则你会得到不一致的数据。

<小时/>

更新 1

您的查询不起作用的原因:

SELECT 
  o.order_id, 
  o.name, 
  o.value, 
  GROUP_CONCAT(o.order_id SEPARATOR ',') AS Options 
FROM order_option AS o 
LEFT JOIN order AS oo on o.order_id = oo.order_id 
where oo.order_status_id = 2 
group by o.order_id

因为 SELECT 子句中的两列 o.name, o.value 既不在聚合函数中，也不在 GROUP BY 中> 子句，因此您得到的这两个列的值不一致，MySQL 为它们获取任意值。试试这个:

SELECT 
  o.order_id, 
  o.name, 
  o.value, 
  GROUP_CONCAT(o.order_id SEPARATOR ',') AS Options 
FROM order_option AS o 
LEFT JOIN order AS oo on o.order_id = oo.order_id 
where oo.order_status_id = 2 
group by o.order_id, 
         o.name,
         o.value;