php - 如何制作畅销书排行榜？

Question

我想使用以前订单的信息制作“畅销书”列表。我目前拥有的是这样的；

Product Quantity
2227    30
1722    3
1851    7
2227    10
1722    4
1863    1
etc....

第一列(产品)是数据库中每个产品的唯一 ID。数量当然是指已售出的商品数量。每一行对应一个订单。因此 ID 2227 在此列表中出现两次。

如何对这些数据进行排序，以便获得 ID 2227 的总共销售次数？

我现在的 PHP 是:

$SQL_best = "SELECT c.company, co.id, cod.productId, cod.quantity 
FROM customers c 
LEFT JOIN customers_orders co ON c.id = co.custId 
LEFT JOIN customers_orders_details cod ON co.id = cod.orderId 
WHERE c.reseller =1 
AND c.status != 99 
AND c.id = ".$intCustomerId; 

$result_best = $objDB->sqlExecute($SQL_best);

*some html code here*

<table style="margin:0px auto;">
<tr>
    <th>Product</th>
    <th>Quantity</th>
</tr>
<?php
while($obj_best = $objDB->getObject($result_best)) {
    if ($obj_best->quantity > 0) { // don't include negatve quantaties (RMA's / refunds)
    echo "<tr>";
        echo "<td>".$obj_best->productId."</td>";
        echo "<td>".$obj_best->quantity."</td>";
    echo "</tr>";
    }
}
?>
</table>

MySQL查询

所以我需要将所有 $obj_best->productId 加在一起。在这种情况下我该怎么做？或者我应该编辑我的查询？

Answer 1

这是一个想法，你可以像这样更改 SQL 字符串:

$SQL_best = "SELECT c.company, co.id, cod.productId,
SUM(cod.quantity) AS quantity
FROM customers c 
LEFT JOIN customers_orders co ON c.id = co.custId 
LEFT JOIN customers_orders_details cod ON co.id = cod.orderId 
WHERE c.reseller = 1 
AND c.status != 99 
AND c.id = " . $intCustomerId . "
GROUP BY cod.productId
HAVING quantity > 0
ORDER BY quantity DESC";