数据库查询问题
数据库有个日志表log: 里面有很多用户登录的数据,表结构如下:ID,页面,时间
ID , ACTION,TIME
A 1 2012-5-1 12:00
A 2 2012-5-1 11:00
B 1 2012-5-1 9:00
B 25 2012-5-1 9:00.
B 2 2012-5-2 9:00
A 5 2012-5-2 9:00
B 4 2012-5-2 9:00
C 9 2012-5-2 9:00
我想知道 每天登录用户是多少?
这边会出现一个用户一天内几次记录,我就把他算作一次
[解决办法]
select count(distinct id) ,DATE_FORMAT(time,'%Y%m%d') from table_name group by DATE_FORMAT(time,'%Y%m%d')
[解决办法]
select count(distinct id) as user_num,count(1) as act_num,DATE_FORMAT(time,'%Y%m%d') as dt from table_name group by DATE_FORMAT(time,'%Y%m%d')
[解决办法]
select id,substring(DATE_FORMAT(time,'%Y%m%d'),1,8),count(*)
from table_name
group by id,substring(DATE_FORMAT(time,'%Y%m%d'),1,8)
[解决办法]
select substring(DATE_FORMAT(TIME,'%Y%m%d'),1,8),count(distinct ID)from table_name group by substring(DATE_FORMAT(TIME,'%Y%m%d')