| MySQL分析函数实现
还好MySQL8.0已经实现了与Oracle相同的分析函数。
1. 实现rownum
SET @rn:=0;
SELECT @rn:=@rn+1 AS rownum ,e.* FROM emp e;
或者写成:
SELECT @rn:=@rn + 1 AS rownum ,e.* FROM emp e ,(SELECT @rn:=0) c
2. 各种分析函数写法 (MySQL实现分析语句时可能遇到的各种计算问题)
2.1 sum() 实现
--SQL 执行顺序 ,FROM ,JOIN ,WHERE ,GROUP BY,HAVING ,ORDER BY ,SELECT,
在Oracle中分页语句的原始语句如下:
SELECT E.*, SUM(SAL) OVER(PARTITION BY DEPTNO) AS COUNTOVER FROM EMP E;
SELECT E.*,
(SELECT SUMOVER
FROM (SELECT DEPTNO, SUM(SAL) AS SUMOVER
FROM EMP E1
GROUP BY DEPTNO) X
WHERE X.DEPTNO = E.DEPTNO) AS COUNTOVER
FROM EMP E
ORDER BY DEPTNO;
Mysql中也是这么实现的:
SELECT E.*,
(SELECT SUMOVER
FROM (SELECT DEPTNO, SUM(SAL) AS SUMOVER
FROM emp E1
GROUP BY DEPTNO) X
WHERE X.DEPTNO = E.DEPTNO) AS COUNTOVER
FROM emp E
ORDER BY DEPTNO;
2.2 row_number () 实现
select
e.* ,row_number() over(partition by deptno order by empno) as ROW_NUMBER from emp e;
我们的默认规则是在from后初始化变量。
SELECT E.*,
IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS ROW_NUMBER,
@DEPTNO := DEPTNO AS VAR1
FROM EMP E, (SELECT @DEPTNO := '', @RN := 0) C
ORDER BY DEPTNO;
SELECT E.*,
IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS ROW_NUMBER,
@DEPTNO := DEPTNO AS VAR1
FROM EMP E, (SELE