数据库查询有关问题

数据库查询问题
数据库有个日志表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

我想知道每天登录用户是多少?
这边会出现一个用户一天内几次记录，我就把他算作一次

[解决办法]

SQL code

select count(distinct id) ,DATE_FORMAT(time,'%Y%m%d') from table_name group by DATE_FORMAT(time,'%Y%m%d')
[解决办法]
SQL codeselect 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)
[解决办法]
SQL codeselect substring(DATE_FORMAT(TIME,'%Y%m%d'),1,8),count(distinct ID)from table_name  group by substring(DATE_FORMAT(TIME,'%Y%m%d')

数据库查询有关问题

热点推荐