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 实现
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;
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:
SELECT MESSAGE_ID, GET_USER_ID, SEND_USER_ID, MESSAGE_CONTEXT, CREATE_TIME FROM ( SELECT SRC.*, IF ( @V_USER_ID = USER_ID ,@V_RANK := @V_RANK + 1 ,@V_RANK := 1 ) AS RANK ,@V_USER_ID := USER_ID AS G_USER_ID FROM ( SELECT MESSAGE_ID, USER_ID, CREATE_TIME, MESSAGE_CONTEXT, GET_USER_ID, SEND_USER_ID FROM ( SELECT MESSAGE_ID, GET_USER_ID, SEND_USER_ID, GET_USER_ID AS USER_ID, CREATE_TIME, MESSAGE_CONTEXT FROM T_SD_MESSAGE WHERE GET_USER_ID != '1234' UNION ALL SELECT MESSAGE_ID, GET_USER_ID, SEND_USER_ID, SEND_USER_ID AS USER_ID, CREATE_TIME, MESSAGE_CONTEXT FROM T_SD_MESSAGE WHERE SEND_USER_ID != '1234' ) METADATA ORDER BY USER_ID ASC, CREATE_TIME DESC ) SRC, ( SELECT @V_RANK = 0, @V_USER_ID := NULL ) VARS ) SRC WHERE RANK = 1 ORDER BY CREATE_TIME DESC
一个过程;
DROP PROCEDURE IF EXISTS PROCE_USER_NEW_MSG; DROP TEMPORARY TABLE IF EXISTS TEM_USER_NEW_MSG; DELIMITER || CREATE PROCEDURE PROCE_USER_NEW_MSG ( IN FRIST_RESULT INT, IN FETCH_SIZE INT ) BEGIN SELECT M.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_HEADER FROM T_SD_MESSAGE M LEFT 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_ID LEFT JOIN T_SD_GENERAL_PICTURE S_H ON S_H.GENERAL_PICTURE_ID = S_U.USER_HEADER_PIC_ID WHERE M.MESSAGE_ID IN ( SELECT MESSAGE_ID FROM ( SELECT MESSAGE_ID, RANK, MESSAGE_CONTEXT, CREATE_TIME FROM ( SELECT SRC.*, IF ( @V_USER_ID = USER_ID ,@V_RANK := @V_RANK + 1 ,@V_RANK := 1 ) AS RANK ,@V_USER_ID := USER_ID AS G_USER_ID FROM ( SELECT MESSAGE_ID, USER_ID, CREATE_TIME, MESSAGE_CONTEXT, GET_USER_ID, SEND_USER_ID FROM ( SELECT MESSAGE_ID, GET_USER_ID, SEND_USER_ID, GET_USER_ID AS USER_ID, CREATE_TIME, MESSAGE_CONTEXT FROM T_SD_MESSAGE WHERE GET_USER_ID != '3000' UNION ALL SELECT MESSAGE_ID, GET_USER_ID, SEND_USER_ID, SEND_USER_ID AS USER_ID, CREATE_TIME, MESSAGE_CONTEXT FROM T_SD_MESSAGE WHERE SEND_USER_ID != '3000' ) METADATA ORDER BY USER_ID ASC, CREATE_TIME DESC ) SRC, ( SELECT @V_RANK = 0, @V_USER_ID := NULL ) VARS ) SRC WHERE RANK = 1 ORDER BY CREATE_TIME DESC ) SRC ) ORDER BY M.CREATE_TIME DESC LIMIT FRIST_RESULT, FETCH_SIZE ; END|| DELIMITER ; -- LIMIT FRIST_RESULT ,FETCH_SIZE CALL PROCE_USER_NEW_MSG (0, 2);
转自: http://ace105.blog.51cto.com/639741/724411