如何编写一个sql查询,根据从不同表中选择的条件来显示两个表中的项。
我有表格:
- Customer表有列CustomerID、Name、Address和Tel
- CustomerOrder表有列CustomerID、OrderID、Date、TotalAmount和Status
- OrderItem表有列OrderID、ProductCode、UnitPrice、Qty、TotalPrice
因此,当选择CustomerID时,我希望订单显示在这3个表中。以便在下面显示客户曾经下过的所有订单。我尝试使用查询:
Select CustomerOrder.*, OrderItem.*
From CustomerOrder
INNER JOIN OrderItem Where Customer.CustomerID = $CustomerID
但它不起作用。在查询以及使用php正确显示数据方面需要帮助。
有人能帮忙吗?
例如
CustomerID:__________
OrderID:__1____ Date:______ TotalAmount:______ Status:_____
ProductCode:__ UnitPrice:___ Qty:_____TotalPrice:__________
ProductCode:___ UnitPrice:______ Qty:_____ TotalPrice:_________
OrderID:___2___ Date:______ TotalAmount:______ Status:_____
ProductCode:__ UnitPrice:___ Qty:_____TotalPrice:__________
ProductCode:___ UnitPrice:______ Qty:_____ TotalPrice:_________
由于在这一步中不需要显示客户信息,因此查询可以是:
SELECT co.OrderID
,co.Date
,co.TotalAmount
,co.Status
,ci.ProductCode
,ci.UnitPrice
,ci.Qty
,ci.TotalPrice
FROM CustomerOrder AS co
INNER JOIN OrderItem AS ci ON (ci.orderID = co.OrderID)
WHERE co.CustomerID = $customer
ORDER BY co.OrderID, ci.ProductCode
由于您希望输出中没有重复的订单信息,因此您的PHP代码应该是这样的:
$current_order_id = false;
foreach ($data as $row) {
if ($current_order_id!==$row['OrderID']) {
$current_order_id = $row['OrderID'];
echo "OrderID: ".$row['OrderID']." , Date: ".$row['Date']." , TotalAmount: ".$row['TotalAmount']." , Status: ".$row['Status']." <br>";
}
echo "ProductCode: ".$row['ProductCode']." , UnitPrice: ".$row['UnitPrice']." , Qty: ".$row['Qty']." , TotalPrice: ".$row['TotalPrice']." <br>";
}
另一种方法是首先仅从CustomerOrder
获取所有订单信息。然后对结果进行循环,得到每个订单的项目信息OrderItem
。
这样尝试:SELECT co.OrderID,date,TotalAmount,ProductCode, UnitPrice,Qty,TotalPrice,Status FROM CustomerOrder AS co
INNER JOIN OrderItem AS oi ON oi.orderID = co.OrderID
INNER JOIN Customer AS c ON c.CustomerID = co.CustomerID
WHERE c.CustomerID = $customer