我有两个（mysql）表：
orders:
order_number
deliveries:
id, order_number, status
订单在交货表中可以有多个交货。现在，我查询50个订单，然后循环它们（php）并查询deliverys表中的deliverys，其中order_number=orders.order_number并合并数组。
理想情况下，这是一个查询，而不是51。我已经尝试了100种联接和组的组合，但无法破解这个看似简单的查询。结果应该是：
[orders] => Array
(
[0] => Array
(
[order_number] => 16021322220558
[deliveries] => Array
(
[0] => Array
(
[id] => 13332
[order_number] => 16021322220558
[status] => delivering
)
[1] => Array
(
[id] => 13333
[order_number] => 16021322220558
[status] => delivering
)
)
)
[1] => Array
(
[order_number] => 16021322220559
[deliveries] => Array
(
[0] => Array
(
[id] => 13334
[order_number] => 16021322220559
[status] => delivering
)
[1] => Array
(
[id] => 13335
[order_number] => 16021322220559
[status] => delivering
)
)
)
...
)
编辑：这是我最终得到的结果。IN是从51个查询减少到2个的洞察力
<?php
// Query for the last 50 orders
$q_orders['orders'] = // SELECT * FROM orders LIMIT 50
// Create array of order numbers
foreach($q_orders['orders'] as $order){
$order_numbers[] = $order['order_number'];
}
// Query for all the deliveries
$q_deliveries = // SELECT * FROM deliveries WHERE order_number IN ($order_numbers)
// For each order, append the deliveries with the same order_number
$i = 0;
foreach($q_orders['orders'] as $order){
foreach($q_deliveries as $delivery){
if($delivery['order_number'] == $order['order_number']){
$q_orders['orders'][$i]['deliveries'][] = $delivery;
}
}
$i++;
}
好吧，根据我所能收集到的，我会说：
SELECT id, orders.order_number, status
FROM orders, deliveries
WHERE orders.order_number = 'ordernumber'
AND deliveries.order_number = orders.order_number
然而，我很困惑为什么你首先需要原始订单号。你可以直接选择deliveries.order_number，不是吗？像这样：
SELECT id, order_number, status
FROM deliveries
WHERE order_number = 'ordernumber'
现在，对于一些PHP魔术（我相信这应该会有所不同，尽管我现在无法测试它）
<?php
$result = mysql_query($query);
$result_array = array();
while($row = mysql_fetch_array($result)){
$result_array[] = $row;
}
?>
对不起，我相信我现在明白你的问题了。在这种情况下，不，您不能使用mysql返回一个基于php的自然多维数组。（mysql无法格式化特定语言的结果）
如果要更改结果的排序方式，则必须手动合并数组。
编辑：用于多个订单查询
SELECT id, order_number, status
FROM deliveries
WHERE order_number IN (2323, 34, 25, 44646, 24, 26)
您将希望使用IN。这意味着查询将获取这些订单号中的任何一个。用php数组填充IN非常容易，如下所示：
<?php
$ordernumbers = implode(",", $arrayWithOrderNumbers);
$query = "SELECT id, order_number, status
FROM deliveries
WHERE order_number IN ($ordernumbers)";
?>
IN基本上就是这个的缩写：WHERE order_number = 34 OR order_number = 35 OR order_number = 34
等等。等等