Programing Language/Database
mysql) 이번주 또는 저번주 방문자 통계 sql 문
Jude_Song
2023. 2. 18. 00:47
728x90
반응형
- 월요일에서부터 일요일까지 기준으로 통계 SELECT SQL
CREATE TABLE `statistics` (
`no` int(11) unsigned NOT NULL AUTO_INCREMENT,
`date` timestamp NULL DEFAULT NULL,
`value` int(11) DEFAULT NULL,
PRIMARY KEY (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
- 이번주(월 ~ 일)
SELECT date, DATE_FORMAT(`date`, '%a') AS `week`, value AS total from statistics_table where date_format(date,'%Y-%m-%d')
BETWEEN
(SELECT ADDDATE(CURDATE(), - WEEKDAY(CURDATE()) + 0 ))
AND
(SELECT ADDDATE(CURDATE(), - WEEKDAY(CURDATE()) + 6 ))
order by date desc
- 저번주 (월 ~ 일)
SELECT date, DATE_FORMAT(`date`, '%a') AS `week`, value AS total from statistics_table where date_format(date,'%Y-%m-%d')
BETWEEN
(SELECT ADDDATE(CURDATE(), - WEEKDAY(CURDATE()) - 7 ))
AND
(SELECT ADDDATE(CURDATE(), - WEEKDAY(CURDATE()) - 1 ))
order by date desc
참고되셨으면 하단의 광고 클릭센스!!
728x90
반응형