统计昨日、今日、上周、本周、上月、本月、上年度、本年度、全部访问量
your_tablename 替换为自己的表名
date_time替换为自己的表中时间字段
-- 合并今日至今年访问量
(SELECT 'today' AS visit_period, COUNT(*) AS visits
FROM your_tablename
WHERE DATE(date_time) = CURDATE())
UNION ALL
-- 昨日访问量
(SELECT 'yesterday', COUNT(*)
FROM your_tablename
WHERE DATE(date_time) = CURDATE() - INTERVAL 1 DAY)
UNION ALL
-- 本周访问量
(SELECT 'this_week', COUNT(*)
FROM your_tablename
WHERE YEARWEEK(date_time, 1) = YEARWEEK(CURDATE(), 1))
UNION ALL
-- 上周访问量
(SELECT 'last_week', COUNT(*)
FROM your_tablename
WHERE YEARWEEK(date_time, 1) = YEARWEEK(CURDATE(), 1) - 1)
UNION ALL
-- 本月访问量
(SELECT 'this_month', COUNT(*)
FROM your_tablename
WHERE MONTH(date_time) = MONTH(CURDATE()) AND YEAR(date_time) = YEAR(CURDATE()))
UNION ALL
-- 上月访问量
(SELECT 'last_month', COUNT(*)
FROM your_tablename
WHERE MONTH(date_time) = MONTH(CURDATE()) - 1 AND (MONTH(CURDATE()) = 1 OR YEAR(date_time) = YEAR(CURDATE()) - 1))
UNION ALL
-- 本年访问量
(SELECT 'this_year', COUNT(*)
FROM your_tablename
WHERE YEAR(date_time) = YEAR(CURDATE()))
UNION ALL
-- 去年访问量
(SELECT 'last_year', COUNT(*)
FROM your_tablename
WHERE YEAR(date_time) = YEAR(CURDATE()) - 1)
UNION ALL
-- 所有访问量
(SELECT 'total_visits', COUNT(*)
FROM your_tablename);
原文地址:https://blog.csdn.net/a913222/article/details/136153320
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!