mysql下日月年周查询

浏览数：119 / 时间：2015年06月12日

DATE_FORMAT(FROM_UNIXTIME(createDate),‘%Y-%m‘) = DATE_FORMAT(Now(),‘%Y-%m‘) 月
DATE(FROM_UNIXTIME(L.`createDate`))=DATE(Now()) 日

WEEK(DATE(FROM_UNIXTIME(`createDate`)),1) = WEEK(DATE(NOW()),1) 周

SP :
BEGIN
#1,代表天，2代表月，3代表季度
#SET @mycnt = 0;
#(@mycnt:= @mycnt + 1) as rank
IF _type = 1 THEN

# SELECT UNIX_TIMESTAMP("2014-07-04");
#leave sp;

SET @query = ‘SELECT L.`memberID`,sum(L.`loginLong`) AS loginLongTotal,M.name as memberName,M.photo as memberPhoto FROM Dog_Login AS L LEFT JOIN Dog_Member AS M ON L.memberID = M.id WHERE DATE(FROM_UNIXTIME(L.`createDate`))=DATE(Now()) GROUP BY L.memberID order by loginLongTotal desc‘;
SET @limit = CONCAT(‘ LIMIT ‘, (_page - 1)*_limit, ‘,‘, _limit);
SET @query = CONCAT(@query,@limit);

ELSEIF _type = 2 THEN

SET @query = ‘SELECT L.`memberID`,sum(L.`loginLong`) AS loginLongTotal,M.name as memberName,M.photo as memberPhoto FROM Dog_Login AS L LEFT JOIN Dog_Member AS M ON L.memberID = M.id WHERE DATE_FORMAT(FROM_UNIXTIME(L.`createDate`),"%Y-%m") = DATE_FORMAT(Now(),"%Y-%m") GROUP BY L.memberID order by loginLongTotal desc‘;
SET @limit = CONCAT(‘ LIMIT ‘, (_page - 1)*_limit, ‘,‘, _limit);
SET @query = CONCAT(@query,@limit);

ELSEIF _type = 3 THEN

       IF DATE_FORMAT(Now(),‘%Y-%m‘) >= DATE_FORMAT(Now(),"%Y-01") AND DATE_FORMAT(Now(),‘%Y-%m‘) <= DATE_FORMAT(Now(),"%Y-03") THEN

       SET @query = ‘SELECT L.`memberID`,sum(L.`loginLong`) AS loginLongTotal,M.name as memberName,M.photo as memberPhoto FROM Dog_Login AS L LEFT JOIN Dog_Member AS M ON L.memberID = M.id WHERE DATE_FORMAT(FROM_UNIXTIME(L.`createDate`),"%Y-%m") >= DATE_FORMAT(Now(),"%Y-01") AND DATE_FORMAT(FROM_UNIXTIME(L.`createDate`),"%Y-%m") <= DATE_FORMAT(Now(),"%Y-03") GROUP BY L.memberID order by loginLongTotal desc‘;
       SET @limit = CONCAT(‘ LIMIT ‘, (_page - 1)*_limit, ‘,‘, _limit);
       SET @query = CONCAT(@query,@limit);

       ELSEIF DATE_FORMAT(Now(),‘%Y-%m‘) >= DATE_FORMAT(Now(),‘%Y-04‘) AND DATE_FORMAT(Now(),‘%Y-%m‘) <= DATE_FORMAT(Now(),‘%Y-06‘) THEN

       SET @query = ‘SELECT L.`memberID`,sum(L.`loginLong`) AS loginLongTotal,M.name as memberName,M.photo as memberPhoto FROM Dog_Login AS L LEFT JOIN Dog_Member AS M ON L.memberID = M.id WHERE DATE_FORMAT(FROM_UNIXTIME(L.`createDate`),"%Y-%m") >= DATE_FORMAT(Now(),"%Y-04") AND DATE_FORMAT(FROM_UNIXTIME(L.`createDate`),"%Y-%m") <= DATE_FORMAT(Now(),"%Y-06") GROUP BY L.`memberID` order by loginLongTotal desc‘;
       SET @limit = CONCAT(‘ LIMIT ‘, (_page - 1)*_limit, ‘,‘, _limit);
       SET @query = CONCAT(@query,@limit);

       ELSEIF DATE_FORMAT(Now(),‘%Y-%m‘) >= DATE_FORMAT(Now(),"%Y-07") AND DATE_FORMAT(Now(),‘%Y-%m‘) <= DATE_FORMAT(Now(),"%Y-09") THEN
       SET @query = ‘SELECT L.`memberID`,sum(L.`loginLong`) AS loginLongTotal,M.name as memberName,M.photo as memberPhoto FROM Dog_Login AS L LEFT JOIN Dog_Member AS M ON L.memberID = M.id WHERE DATE_FORMAT(FROM_UNIXTIME(L.`createDate`),"%Y-%m") >= DATE_FORMAT(Now(),"%Y-07") AND DATE_FORMAT(FROM_UNIXTIME(L.`createDate`),"%Y-%m") <= DATE_FORMAT(Now(),"%Y-09") GROUP BY L.memberID order by loginLongTotal desc‘;
       SET @limit = CONCAT(‘ LIMIT ‘, (_page - 1)*_limit, ‘,‘, _limit);
       SET @query = CONCAT(@query,@limit);

       ELSEIF DATE_FORMAT(Now(),‘%Y-%m‘) >= DATE_FORMAT(Now(),"%Y-10") AND DATE_FORMAT(Now(),‘%Y-%m‘) <= DATE_FORMAT(Now(),"%Y-12") THEN
       SET @query = ‘SELECT L.`memberID`,sum(L.`loginLong`) AS loginLongTotal,M.name as memberName,M.photo as memberPhoto FROM Dog_Login AS L LEFT JOIN Dog_Member AS M ON L.memberID = M.id WHERE DATE_FORMAT(FROM_UNIXTIME(L.`createDate`),"%Y-%m") >= DATE_FORMAT(Now(),"%Y-10") AND DATE_FORMAT(FROM_UNIXTIME(L.`createDate`),"%Y-%m") <= DATE_FORMAT(Now(),"%Y-12") GROUP BY L.memberID order by loginLongTotal desc‘;
       SET @limit = CONCAT(‘ LIMIT ‘, (_page - 1)*_limit, ‘,‘, _limit);
       SET @query = CONCAT(@query,@limit);
       END IF;
ELSE
    LEAVE SP ;
END IF;

PREPARE stmt FROM @query;
EXECUTE stmt;

IF ROW_COUNT() <> 1
THEN SET _RTN = 1300 ;
LEAVE SP ;
END IF ;
SET _RTN = 1 ;
SELECT _RTN ;

END

SP :
BEGIN
SET @d = "day";
SET @w = "week";
SET @m = "month";
SET @y = "year";

(SELECT `stepNum`,FROM_UNIXTIME(`createDate`) as createDate,@d as mark FROM Dog_Pedometer WHERE DATE(FROM_UNIXTIME(`createDate`)) = DATE(Now()) AND kalaID = _kalaID AND memberID = _memberID ORDER BY `stepNum` desc limit 1)
union
(SELECT `stepNum`,FROM_UNIXTIME(`createDate`) as createDate,@w as mark FROM Dog_Pedometer WHERE WEEK(DATE(FROM_UNIXTIME(`createDate`)),1) = WEEK(DATE(NOW()),1) AND kalaID = _kalaID AND memberID = _memberID ORDER BY `stepNum` desc limit 1)
union
(SELECT `stepNum`,FROM_UNIXTIME(`createDate`) as createDate,@m as mark FROM Dog_Pedometer WHERE DATE_FORMAT(FROM_UNIXTIME(`createDate`),‘%Y-%m‘) = DATE_FORMAT(Now(),‘%Y-%m‘) AND kalaID = _kalaID AND memberID = _memberID ORDER BY `stepNum` desc limit 1)
union
(SELECT `stepNum`,FROM_UNIXTIME(`createDate`) as createDate,@y as mark FROM Dog_Pedometer WHERE DATE_FORMAT(FROM_UNIXTIME(`createDate`),‘%Y‘) = DATE_FORMAT(Now(),‘%Y‘) AND kalaID = _kalaID AND memberID = _memberID ORDER BY `stepNum` desc limit 1);
IF ROW_COUNT() <> 1 THEN
SET _RTN = 1300 ;
LEAVE SP ;
END IF ;
SET _RTN = 1 ;
SELECT _RTN ;

END