mysql 連續天數_mysql計算連續天數，mysql連續登入天數，連續天數統計

mysql計算連續天數，mysql連續登入天數，連續天數統計

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

蕃薯耀 2016年11月28日 09:29:10 星期一

http://fanshuyao.iteye.com/

mysql計算連續天數，mysql連續登入天數，連續天數統計：

http://fanshuyao.iteye.com/blog/2341455

Oracle計算連續天數，計算連續時間，Oracle連續天數統計

http://fanshuyao.iteye.com/blog/2341163

一、表結構及初始化資料

DROP TABLE user_login;

CREATE TABLE user_login(

pid INT NOT NULL,

login_time DATETIME NOT NULL

);

INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-25 13:30:45');

INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-24 13:30:45');

INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-24 10:30:45');

INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-24 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-23 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-10 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-09 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-01 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(1,'2016-10-31 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-25 13:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-24 13:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-23 10:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-22 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-21 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-20 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-19 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-02 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-01 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-10-31 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-10-30 09:30:45');

INSERT INTO user_login(pid, login_time) VALUES(2,'2016-10-29 09:30:45');

二、封裝計算連續天數的方法

DELIMITER $$

CREATE

FUNCTION f_continuty_days(id INT, start_time DATE, end_time DATE)

RETURNS INT

BEGIN

DECLARE days INT;

DECLARE flag INT;

DECLARE previous_day DATE;

SET days := 0;

SET flag := 1;

SET previous_day := DATE_SUB(end_time,INTERVAL 1 DAY);

WHILE flag>0 DO

SELECT COUNT(DISTINCT(DATE(login_time))) INTO flag FROM user_login

WHERE pid = id

AND DATE(login_time) = previous_day ;

IF flag > 0 THEN

SET days := days + 1;

SET previous_day := DATE_SUB(previous_day,INTERVAL 1 DAY);

END IF;

END WHILE;

RETURN days;

END$$

DELIMITER ;

三、調用方法

把使用者pid、開始時間、結束時間參數傳進去。

SELECT f_continuty_days(1,DATE('2016-10-01'),DATE('2016-11-25'));

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

蕃薯耀 2016年11月28日 09:29:10 星期一

http://fanshuyao.iteye.com/