选择过去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();

推荐答案

使用MySQL

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屋！