MySQL实现Oracle的row_number()over(partition by)
来源:互联网 发布:手机淘宝网司法拍卖网 编辑:程序博客网 时间:2024/05/21 20:23
在项目开发中遇到数据库从Oracle换成MySQL,遇见很多sql语法错误;
在网上找到解决问题并摘录下来希望以后碰见这些问题的朋友们可以看到以方便解决自己的问题
(***本文转自http://www.databasesql.info/article/7960256392)
MySQL-to achieve ORACLE-ROW_NUMBER () over (partition by) packet sorting capabilities.
Not provide a similar MYSQL ORACLE in OVER () such a wealth of analysis functions. Need to implement such a function in MySQL, we can only use some flexible approach:
First of all, we create instance data:
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) the identification of needs: grouped according to the department, the staff in the department ranked by salary ranking.
Showing results are expected to be as follows:
+ ------- + -------- + ---------- + ------ +
| 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 |
+ ------- + -------- + ---------- + ------ +
3 SQL to achieve
select empid, deptid, salary, rank from (
select heyf_tmp.empid, heyf_tmp.deptid, heyf_tmp.salary, @ rownum: = @ rownum +1,
if (@ pdept = heyf_tmp.deptid, @ rank: = @ rank +1, @ rank: = 1) as rank,
@ Pdept: = heyf_tmp.deptid
from (
select empid, deptid, salary from heyf_t10 order by deptid asc, salary desc
) Heyf_tmp, (select @ rownum: = 0, @ pdept: = null, @ rank: = 0) a) result
;
4 results demonstrates
mysql> select empid, deptid, salary, rank from (
-> Select heyf_tmp.empid, heyf_tmp.deptid, heyf_tmp.salary, @ rownum: = @ rownum +1,
-> If (@ pdept = heyf_tmp.deptid, @ rank: = @ rank +1, @ rank: = 1) as rank,
-> @ Pdept: = heyf_tmp.deptid
-> From (
-> Select empid, deptid, salary from heyf_t10 order by deptid asc, salary desc
->) Heyf_tmp, (select @ rownum: = 0, @ pdept: = null, @ rank: = 0) a) result
->;
+ ------- + -------- + ---------- + ------ +
| 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)
(5) Summary
This SQL is the use of the MYSQL flexible and user variables call through this case, we can learn by analogy to write more wonderful SQL.
Original Source: user variable called hope we can write more exciting SQL giving top priority to this case.
原文http://www.databasesql.info/article/7960256392
Posted in: MySQL practise
- MySQL实现Oracle的row_number()over(partition by)
- MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能.
- MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能
- mysql 实现oracle里面row_number() OVER (PARTITION BY cid,author ORDER BY id DESC) 的方法
- MySQL实现ORALCE的row_number()over(partition by xx)功能
- Mysql实现ROW_NUMBER() over(PARTITION BY column1 order by column2)
- mysql实现ROW_NUMBER() over (PARTITION BY xx ORDER BY ** DESC)
- MySQL替换oracle里ROW_NUMBER () OVER ( PARTITION BY '' ORDER BY ' ' DESC )的方法
- MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能优化
- mysql模拟Oracle(select t.*, row_number() over(partition by...)...实现分组排序功能
- mysql 实现row_number() over(partition by ) 分组排序功能
- mySql 实现排序函数row_number() over(partition by )
- mysql: 实现row_number() over (partition by) 分组排序
- MySql ..IF.. 实现row_number() over(partition by ) 分组排序功能
- Oracle 分析函数 ---OVER(),row_number(),partition by
- row_number() over(PARTITION BY
- row_number() over(partition by ... )
- row_number() over(partition by
- Nim Game
- Nginx启用GZIP压缩网页传输方法
- ActiveMQ
- 稻盛和夫 活法
- [计算几何][凸包][旋转卡壳] 最远距离点对
- MySQL实现Oracle的row_number()over(partition by)
- Qt窗体间传值之public函数接口
- AngularJS基础讲解-AngularJS简介
- mysql 常用update语句
- 基础练习 阶乘计算
- 经典案例之MouseJack
- 将导入的图片变成背景透明的材质球
- sql中使用存储过程得到随机数
- Unity与c++ 结构体中带数组参数传递