选择过去30天的所有订单,并计算每天有多少 [英] selecting all orders for last 30 days, and counting how many per day
问题描述
我正在尝试从一位客户中选择过去30天的所有订单,所以我需要让customer_id ="$ customer_id"并计算每天有多少订单该客户.
I am trying to select all orders for last 30 days from one customer, so I need to have customer_id = "$customer_id" and count how many orders I have per each day for that one customer.
我需要以这样的数组结束
I need to end up with array like this
Array (
[1] => Array (
[orders] => 41
[date] => 2011-06-13 17:43:50
)
[2] => Array (
[orders] => 11
[date] => 2011-07-13 17:43:50
)
[4] => Array (
[orders] => 2
[date] => 2011-12-13 17:43:50
)
and so on... for 30 days, if some day I dont have any orders, I dont need array or [orders] = 0 ...
}
我有一个名为订单"的表,其中包含id,customer_id和日期字段.
I have table named "orders" with id, customer_id and date field.
我发现了这个问题用于计算总计的SQL查询每天的订单数量?,但是它对我没有帮助,或者我不太了解,因为我是初学者.谢谢!
I found this questions SQL query for Calculating Total No. of Orders per Day? but its not helping me, or I dont understand it very well, btw I am beginner. Thanks!
p.s.我要做的是选择过去30天的所有订单.
p.s. what I managed to do, is to select all orders for last 30 days.
$this->db->query("SELECT * FROM orders WHERE customer_id=" . $customer['id'] . " AND date > ADDDATE(CURDATE(), INTERVAL -30 DAY)")->result_array();
推荐答案
Use MySQL EXTRACT function to fetch day from your date field and then group by results according to this. I haven't try it but the following query should work:
SELECT COUNT(*) AS orders, date_field
FROM your_table
WHERE customer_id=$my_cusotmer
GROUP BY EXTRACT(DAY FROM date_field)
这篇关于选择过去30天的所有订单,并计算每天有多少的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!