如何计算最后一周，最后一个月，昨天的字段数？
如何计算最后一周，最后一个月，昨天的字段数？
提问于 2015-01-31 07:13:20
我想得到客户的城市智慧今天，昨天，上周，上个月的客户数。
我想要的结果像
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回答 1
Stack Overflow用户
回答已采纳
发布于 2015-01-31 07:52:04
与此类似：
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天前。但是，如果您需要前一周和前一个月的数据(在这种情况下对我来说没有多大意义)，您可能需要重写时间间隔。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28249210
复制相关文章
相似问题
腾讯云开发者
