MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能
由于MYSQL没有提供类似ORACLE中OVER()这样丰富的分析函数. 所以在MYSQL里需要实现这样的功能,我们只能用一些灵活的办法:
1.首先我们来创建实例数据:
drop table if exists heyf_t10;create table heyf_t10 (empid int ,deptid int ,salary decimal(10,2) );insert into heyf_t10 values(1,10,5500.00),(2,10,4500.00),(3,20,1900.00),(4,20,4800.00),(5,40,6500.00),(6,40,14500.00),(7,40,44500.00),(8,50,6500.00),(9,50,7500.00);
2. 确定需求: 根据部门来分组,显示各员工在部门里按薪水排名名次.
显示结果预期如下:
+-------+--------+----------+------+| empid | deptid | salary | rank |+-------+--------+----------+------+|1 |10 | 5500.00 | 1 ||2 |10 | 4500.00 | 2 ||4 |20 | 4800.00 | 1 ||3 |20 | 1900.00 | 2 ||7 |40 | 44500.00 | 1 ||6 |40 | 14500.00 | 2 ||5 |40 | 6500.00 | 3 ||9 |50 | 7500.00 | 1 ||8 |50 | 6500.00 | 2 |+-------+--------+----------+------+9 rows in set (0.00 sec)
3. SQL 实现
SELECTempid,deptid,salary,rankFROM(SELECTempid,deptid,salary,IF (@pdept = src.deptid ,@rank := @rank + 1 ,@rank := 1) AS rank,@pdept := src.deptid AS gFROM(SELECTempid,deptid,salaryFROMheyf_t10ORDER BYdeptid ASC,salary DESC) src,(SELECT@pdept := NULL ,@rank := 0) var) z;
4. 结果演示
mysql> SELECT-> empid,-> deptid,-> salary,-> rank-> FROM-> (->SELECT-> empid,-> deptid,-> salary,->->IF (-> @pdept = src.deptid ,@rank := @rank + 1 ,@rank := 1->) AS rank,->@pdept := src.deptid AS g-> FROM->(-> SELECT->empid,->deptid,->salary-> FROM->heyf_t10-> ORDER BY->deptid ASC,->salary DESC->) src,->(-> SELECT->@pdept := NULL ,@rank := 0->) var-> ) z;+-------+--------+----------+------+| empid | deptid | salary | rank |+-------+--------+----------+------+|1 |10 | 5500.00 | 1 ||2 |10 | 4500.00 | 2 ||4 |20 | 4800.00 | 1 ||3 |20 | 1900.00 | 2 ||7 |40 | 44500.00 | 1 ||6 |40 | 14500.00 | 2 ||5 |40 | 6500.00 | 3 ||9 |50 | 7500.00 | 1 ||8 |50 | 6500.00 | 2 |+-------+--------+----------+------+9 rows in set (0.00 sec)
我的SQL:
SELECTMESSAGE_ID,GET_USER_ID,SEND_USER_ID,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 AS G_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_ID AS USER_ID,CREATE_TIME,MESSAGE_CONTEXTFROMT_SD_MESSAGEWHEREGET_USER_ID != '1234'UNION ALLSELECTMESSAGE_ID,GET_USER_ID,SEND_USER_ID,SEND_USER_ID AS USER_ID,CREATE_TIME,MESSAGE_CONTEXTFROMT_SD_MESSAGEWHERESEND_USER_ID != '1234') METADATAORDER BYUSER_ID ASC,CREATE_TIME DESC) SRC,(SELECT@V_RANK = 0,@V_USER_ID := NULL) VARS) SRCWHERERANK = 1ORDER BYCREATE_TIME DESC
一个过程;
DROP PROCEDUREIF EXISTS PROCE_USER_NEW_MSG;DROP TEMPORARY TABLEIF EXISTS TEM_USER_NEW_MSG;DELIMITER || CREATE PROCEDURE PROCE_USER_NEW_MSG (IN FRIST_RESULT INT,IN FETCH_SIZE INT)BEGINSELECTM.MESSAGE_ID,M.GET_USER_ID,M.SEND_USER_ID,M.MESSAGE_CONTEXT,M.CREATE_TIME,G_U.USER_NAME AS G_USER_NAME,S_U.USER_NAME AS S_USER_NAME,G_H.GENERAL_PIC_THUMBNAIL_URL AS G_HEADER,S_H.GENERAL_PIC_THUMBNAIL_URL AS S_HEADERFROMT_SD_MESSAGE MLEFT JOIN T_SD_USER G_U ON M.GET_USER_ID = G_U.USER_IDLEFT JOIN T_SD_USER S_U ON M.SEND_USER_ID = S_U.USER_IDLEFT 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_ID IN (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 AS G_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_ID AS USER_ID,CREATE_TIME,MESSAGE_CONTEXTFROMT_SD_MESSAGEWHEREGET_USER_ID != '3000'UNION ALLSELECTMESSAGE_ID,GET_USER_ID,SEND_USER_ID,SEND_USER_ID AS USER_ID,CREATE_TIME,MESSAGE_CONTEXTFROMT_SD_MESSAGEWHERESEND_USER_ID != '3000') METADATAORDER BYUSER_ID ASC,CREATE_TIME DESC) SRC,(SELECT@V_RANK = 0,@V_USER_ID := NULL) VARS) SRCWHERERANK = 1ORDER BYCREATE_TIME DESC) SRC)ORDER BYM.CREATE_TIME DESCLIMIT FRIST_RESULT,FETCH_SIZE ;END|| DELIMITER ;-- LIMIT FRIST_RESULT ,FETCH_SIZECALL PROCE_USER_NEW_MSG (0, 2);
转自:http://ace105./639741/724411
