mySQL：子查询到数组？

Question

我正在研究一个稍微复杂（至少对我来说）的 mySQL 查询，其中包含一个子查询，说实话，它不会很好。

SELECT `products`.`id`, `product`.`price`, 
  ( SELECT `value` FROM (`productValues`) 
    WHERE `productValues`.`product` = 'product.id'
  ) as values 
FROM (`products`) WHERE`product`.`active` = 1

当前结果如下所示：

Array
(
    [0] => Array
        (
            [id] => 1
            [active] => 1
            [price] => 1000
            [values] => 
        )
)

我想要的是值元素也成为一个数组，其中包含值表中匹配的所有元素（WHERE ProductValues.product = Product.id）。

我做错了什么？

Answer 1

我不确定你是否可以返回这样的子查询。解决方案是在子查询中使用 GROUP_CONCAT 将所有值连接到一个字符串。然后，在结果中，您可以使用 explode 将此字符串拆分为数组。

更新：子查询只能匹配 1 行
来源： http://dev.mysql.com/doc/refman /5.0/en/subquery-errors.html

Answer 2

SELECT p.id, p.price, pv.`value`
FROM products p
  JOIN  productValues pv
  ON p.product_id=pv.product
WHERE p.active = 1
ORDER BY p.id;

给出一个表，其中每个 pv.value 一行（顺便说一句，不建议使用“value”等保留字）。按 p.id 对输出进行排序可确保特定产品的所有行都在一起。因此，在应用程序层中，循环遍历行，每次 p.id 更改时更改产品。

$old_id=NULL;
$datastructure=array();
while($arr=$result->fetch_assoc()){
  if($arr['id']!=$old_id){
    $datastructure[$arr['id']][price]=$arr['price'];
    $old_id=$arr['id'];
  }
  $datastructure[$arr['id']]['values'][]=$arr['value'];
}

我给出的结构可能比您要求的结构更灵活，因为它允许您通过数组键访问特定产品。

Answer 3

我认为“值”属性始终是一个变量。想想纯 SQL，不管 PHP - 如果您在 sql 编辑器中运行类似的查询 - 您将获得作为一列的值属性。如果您要求检索多于一列，则会出现错误。
一般来说，SQL 查询始终是平坦的 - 一级。
我的建议 - 运行 2 个查询，并将它们放在 PHP 层中。

Answer 4

MySQL只能返回二维结果集，无法返回更复杂的数据结构。您必须进行两个单独的查询并将结果合并到您的应用程序中。

PHP 中的示例：

<?php

//getting a list of products
$result = mysql_query("SELECT `products`.`id`, `product`.`price`
                        FROM (`products`)
                        WHERE`product`.`active` = 1");

$product_ids = array();
$products = array();
while($r = mysql_fetch_assoc($result))
{
    $products[$r['id']] = $r;
    $product_ids[] = $r['id'];
}

$comma_separated_ids = implode(",", $product_ids);

//getting a list of all product values for given products
$result = mysql_query("SELECT `value`, `product`
                        FROM (`productValues`)
                        WHERE `productValues`.`product` IN ('$comma_separated_ids')");

//joining them together
while($r = mysql_fetch_assoc($result))
{
    if (!isset($products[$r['product']]['values']))
        $products[$r['product']]['values'];

    $products[$r['product']]['values'][] = $r['value'];
}

?>

基于 GolezTrol 的答案的更优雅的变体：

<?php
$result = mysql_query("SELECT `products`.`id`, GROUP_CONCAT(`productValues`.`value`) AS `values` 
                        FROM `products`, `productValues`
                        WHERE`product`.`active` = 1 AND `productValues`.`product` = `products`.`id`
                        GROUP BY `products`.`id`");

$products = array();
while($r = mysql_fetch_assoc($result))
{
    $products[$r['id']] = $r;
    $products[$r['id']]['values'] = explode(',', $products[$r['id']]['values']);
}
?>

祝你好运！

Answer 5

当我搜索“mysql 5.7 subquery into array”时，这是最重要的结果，所以即使这个问题很老，它也可以帮助其他人提到，从 MySQL 5.7.22 开始，一个可能的解决方案是使用聚合函数 JSON_ARRAYAGG()，不过使用的是 JOIN 而不是子查询。

更改 Silver Light 给出的示例：

SELECT `products`.`id`, JSON_ARRAYAGG(`productValues`.`value`) AS `values` 
FROM `products`, `productValues`
WHERE`product`.`active` = 1 AND `productValues`.`product` = `products`.`id`
GROUP BY `products`.`id`"

https ://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_json-arrayagg