mysql分组分页_mysql分组分页

DELIMITER $$

USE `ltpdb`$$

DROP PROCEDURE IF EXISTS `file_noteid_page`$$

CREATE DEFINER=`root`@`%` PROCEDURE `file_noteid_page`(i_noteid VARCHAR(64),

i_cid VARCHAR(64),

i_type VARCHAR(64),

i_userid  VARCHAR(64),

i_page_num INT,

i_page_size INT)

BEGIN

DECLARE v_start_index INT ;

DECLARE v_end_index INT ;

DECLARE v_count INT;

SET v_start_index= (i_page_num-1)*i_page_size;

SET v_end_index = i_page_num*i_page_size;

SELECT COUNT(*) INTO v_count FROM (

SELECT  noteid FROM lc_file WHERE

noteid IS NOT NULL

AND  ((i_cid IS NULL && cid IS NULL ) OR cid = i_cid)

AND (i_userid IS NULL OR usrid = i_userid)

AND (i_type IS NULL OR (i_type='4' &&  aclock IS NOT NULL )  OR (i_type IN('1','2','3')  && ftype=i_type  ))

AND (i_noteid IS NULL OR noteid=i_noteid )  GROUP BY noteid ORDER BY cversion DESC ) a;

SET @rw=0;

SELECT  al.noteid ,v_count AS total,rn FROM (

SELECT (@rw := @rw + 1) AS rn ,b.noteid FROM ( SELECT noteid FROM lc_file WHERE

noteid IS NOT NULL

AND  ((i_cid IS NULL && cid IS NULL ) OR cid = i_cid)

AND (i_userid IS NULL OR usrid = i_userid)

AND (i_type IS NULL OR (i_type='4' &&  aclock IS NOT NULL )  OR (i_type IN('1','2','3')  && ftype=i_type  ))

AND (i_noteid IS NULL OR noteid=i_noteid )  GROUP BY noteid  ORDER BY cversion DESC) b ) al

WHERE rn > v_start_index AND  rn <= v_end_index;

END$$

DELIMITER ;

-- ( SELECT noteid FROM lc_file WHERE

noteid IS NOT NULL

AND  ((i_cid IS NULL && cid IS NULL ) OR cid = i_cid)

AND (i_userid IS NULL OR usrid = i_userid)

AND (i_type IS NULL OR (i_type='4' &&  aclock IS NOT NULL )  OR (i_type IN('1','2','3')  && ftype=i_type  ))

AND (i_noteid IS NULL OR noteid=i_noteid )  GROUP BY noteid  ORDER BY cversion DESC)

如果不套一层(select noteid)rn顺序就会乱。