php - 统计今天、本周、上个月和总访问量[MySQL查询]

Question

大家好，这是我的表格的一个非常简化的版本:

我想对该表进行四个 mysql 查询。他们都必须计算总访问量，其中 id_user 等于某个特定的 value 并且 type 不同于 click。一个查询必须计算今天的访问量，另一个查询必须计算本周、月份和总访问量。我不是 MySQL 的专家，我当然可以使用 PHP 解决这个问题，但我更愿意将负载放在我的 sql server 上。感谢您的帮助!

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());