计算今天，本周，上个月的访问次数和总数[MySQL查询] [英] Count visits by today, this week, last month and total [MySQL query]

问题描述

大家好，这是我的桌子的非常简化的版本:

Hello guys this is a very simplified version of my table:

我想对该表进行四个mysql查询.他们都必须计算 id_user 等于某些特定 value 和 type (不同于 click )的总访问次数>.一个查询必须计算今天的访问量，另一个查询要计算本周的访问量，月份和总访问量.我不是MySQL的专家，我当然可以使用PHP解决此问题，但我更喜欢将负载放在我的sql服务器上.感谢您的帮助！

I will like to make four mysql querys to this table. All of them will have to count the total visits where id_user is equal to some particular value and type different from click. One query have to count the visits today, the other in this week, the month and the total visits. Im not very expert on MySQL, i can certainly solve this using PHP but i prefer to put the load on my sql server. Thanks for your help!

推荐答案

试试看.我不知道您的表格叫什么，所以我将其称为trafficTable:

Give these a go. I don't know what your table is called so I have referrered to it as 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());

这篇关于计算今天，本周，上个月的访问次数和总数[MySQL查询]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！