感谢昨天帮忙的一些朋友,说说今天的问题,还是先附上表结构和数据
id uid pid isnewplayer date serverid
1 0000F82 100000000439 0 2011-06-10 5
3 365264242 100000000441 0 2011-06-10 3
4 365264242 100000000441 0 2011-06-10 3
5 365264242 100000000441 0 2011-06-10 3
6 365264242 100000000441 0 2011-06-10 3
7 336633248 100000000442 0 2011-06-10 3
8 231481064 100000000443 0 2011-06-10 3
9 0000F82 100000000439 0 2011-06-10 5
10 0000F82 100000000439 0 2011-06-11 5我需要查询的结果是,每天/每周/每月 至少登陆一次的人数(多次登陆算一次)
日查询sql
select count(pid) as count,date as dates
from
(select DISTINCT pid ,date from login_log where date<='2011-06-11' and date>='2011-06-09' ) tmp
group by dates
结果
count dates
4 2010-06-10
1 2011-06-11周查询sql
select count(pid) as count,DATE_FORMAT(date,"%X-%V")dates from (select DISTINCT pid,date from login_log where date<="2011-06-11" and date>="2011-06-09")tmp group by dates
结果
count dates
5 2011-23
结果错误了,如果一周内登陆过一次去重的话,应该count还是4,因为11号那个pid是属于23周的,说明周查询没有去重,只是累加了2天去重了的count,请问错在哪??
月查询跟周查询错误一样,代码我就不贴了,在线坐等高手解答,谢谢!
id uid pid isnewplayer date serverid
1 0000F82 100000000439 0 2011-06-10 5
3 365264242 100000000441 0 2011-06-10 3
4 365264242 100000000441 0 2011-06-10 3
5 365264242 100000000441 0 2011-06-10 3
6 365264242 100000000441 0 2011-06-10 3
7 336633248 100000000442 0 2011-06-10 3
8 231481064 100000000443 0 2011-06-10 3
9 0000F82 100000000439 0 2011-06-10 5
10 0000F82 100000000439 0 2011-06-11 5我需要查询的结果是,每天/每周/每月 至少登陆一次的人数(多次登陆算一次)
日查询sql
select count(pid) as count,date as dates
from
(select DISTINCT pid ,date from login_log where date<='2011-06-11' and date>='2011-06-09' ) tmp
group by dates
结果
count dates
4 2010-06-10
1 2011-06-11周查询sql
select count(pid) as count,DATE_FORMAT(date,"%X-%V")dates from (select DISTINCT pid,date from login_log where date<="2011-06-11" and date>="2011-06-09")tmp group by dates
结果
count dates
5 2011-23
结果错误了,如果一周内登陆过一次去重的话,应该count还是4,因为11号那个pid是属于23周的,说明周查询没有去重,只是累加了2天去重了的count,请问错在哪??
月查询跟周查询错误一样,代码我就不贴了,在线坐等高手解答,谢谢!
不想要 2011-06-11 这天的话,应写作
date<"2011-06-11"你的条件都是 date<="2011-06-11" and date>="2011-06-09"
这与一般人的理解不一样,通常人们都认为“区间”是包含低端,而不包含高端的
只有一个例外:比如 90年代,是从 1991 到 2000 的
只是在周查询的时候,因为2011-06-10和2011-06-11都属于2011的23周,只要在一周登陆过的账号就把count+1,去除重复的pid,这样表中记录按周来查询的时候count应该是4个对吧?
我想问为什么
select count(pid) as count,DATE_FORMAT(date,"%X-%V")dates from (select DISTINCT pid,date from login_log where date<="2011-06-11" and date>="2011-06-09")tmp group by dates我吧日期按周分组了,再查询不同pid,为什么count是5个!
id uid pid isnewplayer date serverid
1 0000F82 100000000439 0 2011-06-10 5
3 365264242 100000000441 0 2011-06-10 3
4 365264242 100000000441 0 2011-06-10 35 365264242 100000000441 0 2011-06-10 36 365264242 100000000441 0 2011-06-10 37 336633248 100000000442 0 2011-06-10 3
8 231481064 100000000443 0 2011-06-10 3
9 0000F82 100000000439 0 2011-06-10 5
10 0000F82 100000000439 0 2011-06-11 5from
(select DISTINCT pid ,date from login_log where date<='2011-06-11' and date>='2011-06-09' ) tmp
group by dates
select DISTINCT pid,date from login_log where date<="2011-06-11" and date>="2011-06-09"
有问题:
DISTINCT 对整行结果起作用
对
9 0000F82 100000000439 0 2011-06-10 5
10 0000F82 100000000439 0 2011-06-11 5
得到的是
100000000439 2011-06-10
100000000439 2011-06-11你把 DATE_FORMAT(date,"%X-%V") as dates 移到子查询里就对了
FROM (
SELECT DISTINCT pid, DATE_FORMAT( date, "%X-%V" ) dates
FROM login_log
WHERE date <= "2011-06-11"
AND date >= "2011-06-09"
)tmp
GROUP BY dates