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

mysql下日月年周查询