试试这个,如果mysql版本不低的话可以 SELECT siteid, sum( a ) FROM (SELECT siteid, date, count( DISTINCT ip ) a FROM rawlogodd WHERE `date` >= '2005-3-1' AND date <= '2005-3-31' GROUP BY `siteid` , `date` ) b GROUP BY siteid
再执行一次 SELECT `siteid` , `date` , count( DISTINCT ip ) FROM rawlogodd WHERE `date` >= '2005-3-1' AND date <= '2005-3-31' GROUP BY `siteid`
SELECT `siteid` , `date` , count( DISTINCT ip ) FROM rawlogodd WHERE `date` >= '2005-3-1' AND date <= '2005-3-31' GROUP BY `siteid`这个不行,只能得到这样的数据
siteid date count( DISTINCT ip ) 0 2005-03-01 1 3 2005-03-01 2237
这样出来的结果是不是应该是一样的?SELECT siteid , date , count( * ) FROM rawlogodd WHERE date >= '2005-3-1' AND date <= '2005-3-31' GROUP BY siteid , date , ip
SELECT `siteid` , `date` , count( DISTINCT ip ) FROM rawlogodd WHERE `date` >= '2005-3-1' AND date <= '2005-3-31' GROUP BY `siteid` , `date` 改成 SELECT `siteid` , `date` , count( DISTINCT ip ) FROM rawlogodd WHERE `date` >= '2005-3-1' AND date <= '2005-3-31' GROUP BY `siteid` 总的就出来了
试试这样的结果?SELECT siteid, date, count( DISTINCT ip, date ) FROM t1 WHERE date >= '2005-3-1' AND date <= '2005-3-31' GROUP BY siteid
select siteCount=sum(count(distinct ip)),siteid from table where month([date])=3 and year([date])=2005 group by siteid
SELECT siteid, date, count( DISTINCT ip, date ) FROM t1 WHERE date >= '2005-3-1' AND date <= '2005-3-31' GROUP BY siteid 这个对 :)可是想不明白count( DISTINCT ip, date )
SELECT siteid, sum( a )
FROM
(SELECT siteid, date, count( DISTINCT ip ) a
FROM rawlogodd
WHERE `date` >= '2005-3-1'
AND date <= '2005-3-31'
GROUP BY `siteid` , `date`
) b
GROUP BY siteid
SELECT `siteid` , `date` , count( DISTINCT ip )
FROM rawlogodd
WHERE `date` >= '2005-3-1'
AND date <= '2005-3-31'
GROUP BY `siteid`
FROM rawlogodd
WHERE `date` >= '2005-3-1'
AND date <= '2005-3-31'
GROUP BY `siteid`这个不行,只能得到这样的数据
siteid date count( DISTINCT ip )
0 2005-03-01 1
3 2005-03-01 2237
这样出来的结果是不是应该是一样的?SELECT siteid , date , count( * )
FROM rawlogodd
WHERE date >= '2005-3-1'
AND date <= '2005-3-31'
GROUP BY siteid , date , ip
FROM rawlogodd
WHERE `date` >= '2005-3-1'
AND date <= '2005-3-31'
GROUP BY `siteid` , `date`
改成
SELECT `siteid` , `date` , count( DISTINCT ip )
FROM rawlogodd
WHERE `date` >= '2005-3-1'
AND date <= '2005-3-31'
GROUP BY `siteid`
总的就出来了
FROM t1
WHERE date >= '2005-3-1'
AND date <= '2005-3-31'
GROUP BY siteid
SELECT siteid, date, count( DISTINCT ip, date )
FROM t1
WHERE date >= '2005-3-1'
AND date <= '2005-3-31'
GROUP BY siteid
这个对
:)可是想不明白count( DISTINCT ip, date )
实际上就是把同一天同一IP的访问算一次,
然后统计一个月的访问。
所以,distinct ip,date就是这个作用了,
同一天同一小时同一个ip访问不同的网站,第一个访问的网站有效,其它的视为无效。
统计一个月各个网站的ip访问量