如何根据销售额检索我的产品中的前5名?

Question

如何检索表中排名前5位的产品?基于销售额，我使用CodeIgniter，我尝试了如下方法

$conn = new mysqli("localhost", "root","","bubblebee");
if ($conn->connect_errno) { 
printf("Connect failed: %sn", $conn->connect_error);
exit();
} else {
echo 'Connection success <br>' ;
}
$total = "SELECT product_id, SUM(amount) 
FROM order_items 
GROUP BY product_id 
ORDER BY SUM(Amount) DESC 
LIMIT 3";
$statement = $conn->query($total);
if (!$statement = $conn->query($total)) {
echo 'Query failed: ' . $conn->error;
} else {
foreach($statement as $row){
echo $row['product_id'] . '<br>';
}
}

这个

$select = array('products.name as label', 'sum(order_items.amount) as amount');
$final = $this -> db -> select($select)
-> from('products') 
-> join('order_items', 'order_items.product_id = products.id', 'left') 
-> group_by('products.id') 
-> order_by('products.id', 'DESC') 
-> limit(5) 
-> get() -> result_array();

我仍然不能让它工作但我尝试了我在本地主机上的查询它工作了它正在检索prod id和我使用这个查询的金额的总和

SELECT product_id, SUM(amount) FROM order_items GROUP BY product_id ORDER BY SUM(Amount) DESC LIMIT 5

Answer 1

Try

$this->db->select('product_id, SUM(amount)');
$this->db->from('order_items');
$this->db->group_by('product_id');
$this->db->order_by('SUM(amount)', 'DESC');
$this->db->limit(5);
$query = $this->db->get();

sourceSELECT product_id, SUM(amount) FROM order_items GROUP BY product_id ORDER BY SUM(Amount) DESC LIMIT 5