mysql - 如何获取最近 1 周、最近 1 个月、昨天的字段数

Question

我想统计今天、昨天、上周、上个月的城市客户数。

我想要这样的结果

    city today yesterday lastweek lastmnth
     1     23     2         12         12

我的表结构如下

顾客

    c_id  city_id  c_name            currentdate
     1      1       Rama             2015-01-30 09:43:17
     2      1       kavitha          2015-04-30 09:43:17

城市

    city_id   city_name
     1          hyd
     2          Wgl

我尝试了以下。

 select c.c_city, (select count(cr_id)  as lastmonth  from customer 
  where currentdate > DATE(NOW() - INTERVAL 30 DAY) )) from customers as c
  left join cities as ci on c.city_id = ci.city_id group by c.city_id

Answer 1

与此类似:

SELECT city_id as city,
SUM(CASE WHEN LEFT(currentdate,10) = LEFT(NOW(),10) THEN 1 ELSE 0 END) as today,
SUM(CASE WHEN LEFT(currentdate,10) = LEFT(NOW()-INTERVAL 1 DAY,10) THEN 1 ELSE 0 END) as yesterday,
SUM(CASE WHEN currentdate > NOW()-INTERVAL 7 DAY THEN 1 ELSE 0 END) as lastweek,
SUM(CASE WHEN currentdate > NOW()-INTERVAL 30 DAY THEN 1 ELSE 0 END) as lastmnth
FROM customers GROUP BY city_id

对于上周和上个月，我假设您指的是 7 天前和 30 天前。但是，如果您需要上周和上个月的数据(在这种情况下这对我来说意义不大)，您可能需要重写间隔。