mysql oracle over_MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能

DROP PROCEDURE

IF EXISTSPROCE_USER_NEW_MSG;DROP TEMPORARY TABLE

IF EXISTSTEM_USER_NEW_MSG;

DELIMITER||

CREATE PROCEDUREPROCE_USER_NEW_MSG (IN FRIST_RESULT INT,IN FETCH_SIZE INT)BEGIN

SELECTM.MESSAGE_ID,

M.GET_USER_ID,

M.SEND_USER_ID,

M.MESSAGE_CONTEXT,

M.CREATE_TIME,

G_U.USER_NAME ASG_USER_NAME,

S_U.USER_NAME ASS_USER_NAME,

G_H.GENERAL_PIC_THUMBNAIL_URLASG_HEADER,

S_H.GENERAL_PIC_THUMBNAIL_URLASS_HEADERFROMT_SD_MESSAGE MLEFT JOIN T_SD_USER G_U ON M.GET_USER_ID = G_U.USER_ID

LEFT JOIN T_SD_USER S_U ON M.SEND_USER_ID = S_U.USER_ID

LEFT JOIN T_SD_GENERAL_PICTURE G_H ON G_H.GENERAL_PICTURE_ID =G_U.USER_HEADER_PIC_IDLEFT JOIN T_SD_GENERAL_PICTURE S_H ON S_H.GENERAL_PICTURE_ID =S_U.USER_HEADER_PIC_IDWHEREM.MESSAGE_IDIN(SELECTMESSAGE_IDFROM(SELECTMESSAGE_ID,

RANK,

MESSAGE_CONTEXT,

CREATE_TIMEFROM(SELECTSRC.*,IF(@V_USER_ID = USER_ID ,@V_RANK := @V_RANK + 1 ,@V_RANK := 1)AS RANK ,@V_USER_ID := USER_ID ASG_USER_IDFROM(SELECTMESSAGE_ID,USER_ID,

CREATE_TIME,

MESSAGE_CONTEXT,

GET_USER_ID,

SEND_USER_IDFROM(SELECTMESSAGE_ID,

GET_USER_ID,

SEND_USER_ID,

GET_USER_IDAS USER_ID,

CREATE_TIME,

MESSAGE_CONTEXTFROMT_SD_MESSAGEWHEREGET_USER_ID!= '3000'

UNION ALL

SELECTMESSAGE_ID,

GET_USER_ID,

SEND_USER_ID,

SEND_USER_IDAS USER_ID,

CREATE_TIME,

MESSAGE_CONTEXTFROMT_SD_MESSAGEWHERESEND_USER_ID!= '3000') METADATAORDER BY

USER_ID ASC,

CREATE_TIMEDESC) SRC,

(SELECT

@V_RANK = 0,@V_USER_ID := NULL) VARS

) SRCWHERERANK= 1

ORDER BYCREATE_TIMEDESC) SRC

)ORDER BYM.CREATE_TIMEDESCLIMIT FRIST_RESULT,

FETCH_SIZE ;END||DELIMITER ;--LIMIT FRIST_RESULT ,FETCH_SIZE

CALL PROCE_USER_NEW_MSG (0, 2);