伯爵访问，本周，上月和总计[MySQL查询]

Question

好家伙，这是我的表的一个非常简化的版本：

我会想提出四个的MySQL querys此表。他们都将有总访问人数，其中id_user等于某个特定值和型从点击不同。一个查询必须计算今天的访问次数，本周的访问次数，该月的访问次数和总访问次数。我不是MySQL的专家，我当然可以使用PHP解决这个问题，但我更喜欢把负载放在我的SQL服务器上。谢谢你的帮助！

Answer 1

给这些一展身手。我不知道你的桌子叫什么，所以我把它推荐为trafficTable：

-- Visits today 
select count(*) as visits_today 
from trafficTable tt 
where tt.type != 'click' 
and tt.id_user = '19d71' 
and datetime >= curdate(); 

-- Visits this week 
select count(*) as visits_this_week 
from trafficTable tt 
where tt.type != 'click' 
and tt.id_user = '19d71' 
and yearweek(datetime) = yearweek(curdate()); 

-- Visits this month 
select count(*) as visits_this_month 
from trafficTable tt 
where tt.type != 'click' 
and tt.id_user = '19d71' 
and year(datetime) = year(curdate()) 
and month(datetime) = month(curdate()); 

-- Total visits 
select count(*) as total_visits 
from trafficTable tt 
where tt.type != 'click' 
and tt.id_user = '19d71'; 

--- if you want the last month - this help other ppl in other thread 
    select count(*) as visits_this_month 
    from trafficTable tt 
    where tt.type != 'click' 
    and tt.id_user = '19d71' 
    and year(datetime) <= year(curdate()) 
    and month(datetime) <= month(curdate()); 

Answer 2

你可能想看看这个页面：

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

功能month()，date()，curdate()，week()和其他几个人应该做的伎俩