mysql统计查询数据 mysql groupBy 按日期分组

mysql 有一张这样的表，需要根据customer_ip_date这个字段的日期进行group by 排序,得出每天的数量是多少





MySQL 根据日期 group by有两种写法:
1.字符串截取：substring，从第一个字段截取到第十个字符串,然后group  by 

 

SELECT substring(customers_ip_date, 1, 10),count(distinct customers_ip) as days_count FROM  `userLoginTable`
 
WHERE customers_ip_date BETWEEN '2019-12-01 00:00:00' AND '2019-12-31 23:59:59'  group by  substring(customers_ip_date, 1, 10)
2.使用cast函数，进行group by 

 

SELECT cast(customers_ip_date as date) as day_time,count(distinct customers_ip) as days_count FROM  `userLoginTable` 
 
WHERE customers_ip_date BETWEEN '2019-12-01 00:00:00' AND '2019-12-31 23:59:59'  group by  cast(customers_ip_date as date)


以上两种写法都可以实现mysql根据日期group by 的需求
customers_ip_date 字段有索引的情况下，目前在6000W的表查询也是很快就查询出来