mySQL:数组的子查询?

本文介绍了mySQL:数组的子查询?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在处理一个稍微复杂的(至少对我而言)mySQL 查询，其中包含一个子查询，但说实话并不好.

I am working on a slight complex (at least for me) mySQL query containing a subquery and it isn't going to well to be honest.

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] => ) )

我想要的是 values 元素也成为一个数组，其中包含 Values 表中匹配的所有元素(WHERE productValues.product = product.id).

What I want is the values element to also become an array with all elements in the Values table which matches (WHERE productValues.product = product.id).

我做错了什么?

推荐答案

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 更改时更改产品.

gives a table with one row for each pv.value (BTW, using reserved words like 'value' is not recommended). Ordering the output by p.id ensures that all the rows for a particular product are together. So, in the application layer, loop through your rows, changing product each time the p.id changes.

$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']; }

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

The structure I've given is perhaps more flexible than the one you asked for in that it allows you to access a particular product through the array key.