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 != ''
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 != ''
) 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 != ''
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 != ''
) 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
MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能的更多相关文章
-
MYSQL-实现分组排序 对比 ORACLE 和SQLserver用 row_number() over(partition by ) 分组排序功能
以下是个人笔记: 本文是为了理解 row_number() over(partition by ) 和实现各种数据库的分组排序功能 select ROW_NUMBER()over( partitio ...
-
MYSQL-实现ORACLE 和SQLserver数据中- row_number() over(partition by ) 分组排序功能
网上看见了好多例子都基本上是一样的,没有过多的解释,对于一个初学MySQL来说有点难,我把部分转摘过来如下 原文:http://www.cnblogs.com/buro79xxd/archive/20 ...
-
oracle ROW_NUMBER() OVER(PARTITION BY '分组' ORDER BY '排序' DESC) 用法
转载:https://blog.csdn.net/dbagaoshou/article/details/51330829 SELECT * FROM ( SELECT ROW_NUMBER() OVE ...
-
hive：数据库“行专列”操作---使用collect_set/collect_list/collect_all &; row_number()over(partition by 分组字段 [order by 排序字段])
方案一:请参考<数据库“行专列”操作---使用row_number()over(partition by 分组字段 [order by 排序字段])>,该方案是sqlserver,orac ...
-
oracle row_number() over(partition by .. order by ..)和rank() over(partition by .. order by ..) 和dense_rank() over(partition by .. order by ..)的相似点与区别
新建一个测试表 create table dim_ia_test2(device_number varchar2(20),desc2 varchar2(20)) 插入数据后得到: 一.oracle r ...
-
sql 分组取最新的数据sqlserver巧用row_number和partition by分组取top数据
SQL Server 2005后之后,引入了row_number()函数,row_number()函数的分组排序功能使这种操作变得非常简单 分组取TOP数据是T-SQL中的常用查询, 如学生信息管理系 ...
-
row_number和partition by分组取top数据
分组取TOP数据是T-SQL中的常用查询, 如学生信息管理系统中取出每个学科前3名的学生.这种查询在SQL Server 2005之前,写起来很繁琐,需要用到临时表关联查询才能取到.SQL Serve ...
-
去重 ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段) RN
关键字 ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段) RN 按照分组字段进行排序并标编号 ROW_NUMBER() OVER(PARTITIO ...
-
row_number() over partition by 分组聚合
分组聚合,就是先分组再排序,可以的话顺手标个排名:如果不想分组也可以排名:如果不想分组同时再去重排名也可以 ROW_NUMBER() OVER( [PARTITION BY column_1, col ...
随机推荐
-
React Native Changed the World? or Nothing.
RN是一个awesome的技术, facebook很有想法的团队创造出一项新的技术改变了native开发界. 但是RN本身又疑点重重, RN是为了解决什么问题而存在的? 在诞生了一年后, RN又解决了 ...
-
三菱plc输出指示灯不亮怎么办(转载)
三菱plc输出指示灯不亮怎么办?三菱plc输出指示灯故障 时间:2015-10-21 07:31:12编辑:电工栏目:三菱plc 导读:三菱plc输出指示灯不亮故障的原因,三菱plc在使用中出现输出指 ...
-
（转载）tarjan求割点
割点是无向图中去掉后能把图割开的点.dfs时用dfn(u)记录u的访问时间,用low(u)数组记录u和u的子孙能追溯到的最早的节点(dfn值最小).由于无向图的dfs只有回边和树边,且以第一次dfs时 ...
-
Count on the path
Count on the path Time Limit: 5000/2500 MS (Java/Others) Memory Limit: 131072/131072 K (Java/Othe ...
-
利用Python脚本悄无声息的遥控室友电脑开机密码！
整蛊一下室友就行了,切勿用于非法用途! 利用python脚本控制室友windows系统电脑的开机密码.利用random()生成随机数(密码),天知地知,密码只有你自己知道! Python代码分为cli ...
-
使用proxychains 代理终端
最近在国外的vps上搭建了一个ss服务器,在浏览器里面设置socks5代理上网很方便, 但是终端里面却只支持http方式的代理配置,网上有socks转http代理的方式,但是最近发现一个更为简单的方式 ...
-
jenkins持续集成：jenkins+SVN
实现jenkins从svn拉取最新的代码,再执行驱动脚本进行自动化测试 新建一个任务 输入任务名,选“构建一个*风格的软件项目”,点左下角“确定” 丢弃旧的构建,如下设置为保留3天内的10条构建记录 ...
-
linux的基本操作（LAMP环境搭建）
LAMP 环境搭建 经过前部分章节的学习,你已经掌握了linux的基础知识了.但是想成为一名系统管理员恐怕还有点难度,因为好多单位招聘这个职位的时候都要求有一定的工作经验.然而真正的经验一天两天是学不 ...
-
JAVA使用POI如何导出百万级别数据
用过POI的人都知道,在POI以前的版本中并不支持大数据量的处理,如果数据量过多还会常报OOM错误,这时候调整JVM的配置参数也不是一个好对策(注:jdk在32位系统中支持的内存不能超过2个G,而在6 ...
-
Shell解释器(学习笔记四)
一.Shell解释器 shell解释器,用户和操作系统内核之间的桥梁 shell介于操作系统内核与用户之间,负责接收用户输入的操作指令(命令),并运行和解释,将需要执行的操作传递给操作系统内核并执行 ...