mysql max over_max() over()

今天有人问max() over()的需求在mysql中怎么写。

同以前讲过的几个例子一样，虽然mysql中没有分析函数，可max() over()的方法一样很容易实现

max() over()

mysql> set @max_sal=0;

Query OK, 0 rows affected (0.00 sec)

mysql> select if(@max_sal=0,@max_sal:=sal,@max_sal) as max_sal,sal from emp order by sal desc;

+---------+------+

| max_sal | sal |

+---------+------+

| 5000 | 5000 |

| 5000 | 3000 |

| 5000 | 3000 |

| 5000 | 2975 |

| 5000 | 2850 |

| 5000 | 2450 |

| 5000 | 1600 |

| 5000 | 1500 |

| 5000 | 1300 |

| 5000 | 1250 |

| 5000 | 1250 |

| 5000 | 1100 |

| 5000 | 950 |

| 5000 | 800 |

+---------+------+

14 rows in set (0.00 sec)

max() over(partition)

mysql> set @deptno=0,@max_sal=0;

Query OK, 0 rows affected (0.00 sec)

mysql> select @deptno,if(@deptno=deptno,@max_sal,@max_sal:=sal) as max_sal,deptno,sal,@deptno:=deptno from emp order by deptno,sal desc;

+---------+---------+--------+------+-----------------+

| @deptno | max_sal | deptno | sal | @deptno:=deptno |

+---------+---------+--------+------+-----------------+

| 0 | 5000 | 10 | 5000 | 10 |

| 10 | 5000 | 10 | 2450 | 10 |

| 10 | 5000 | 10 | 1300 | 10 |

| 10 | 3000 | 20 | 3000 | 20 |

| 20 | 3000 | 20 | 3000 | 20 |

| 20 | 3000 | 20 | 2975 | 20 |

| 20 | 3000 | 20 | 1100 | 20 |

| 20 | 3000 | 20 | 800 | 20 |

| 20 | 2850 | 30 | 2850 | 30 |

| 30 | 2850 | 30 | 1600 | 30 |

| 30 | 2850 | 30 | 1500 | 30 |

| 30 | 2850 | 30 | 1250 | 30 |

| 30 | 2850 | 30 | 1250 | 30 |

| 30 | 2850 | 30 | 950 | 30 |

+---------+---------+--------+------+-----------------+

14 rows in set (0.00 sec)