本文共 7465 字,大约阅读时间需要 24 分钟。
| MySQL分析函数实现
还好MySQL8.0已经实现了与Oracle相同的分析函数。
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
--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 COUNTOVERFROM EMP EORDER 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 COUNTOVERFROM emp EORDER BY DEPTNO;
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 VAR1FROM EMP E, (SELECT @DEPTNO := '', @RN := 0) CORDER BY DEPTNO;
SELECT E.*, IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS ROW_NUMBER, @DEPTNO := DEPTNO AS VAR1FROM EMP E, (SELECT @DEPTNO := '', @RN := 0) CORDER BY DEPTNO;
这个语句首先执行order by
在Oracle中实现:
SELECT E.*, TRUNC(SAL / SUM(SAL) OVER(PARTITION BY DEPTNO), 3) AS SALPERCENTFROM EMP EORDER BY DEPTNO;
SELECT E.*, SAL / (SELECT SUMOVER FROM (SELECT DEPTNO, SUM(SAL) AS SUMOVER FROM emp E1 GROUP BY DEPTNO) X WHERE X.DEPTNO = E.DEPTNO) AS SalPercentFROM emp EORDER BY DEPTNO;
Oracle:
SELECT e.* ,SUM(sal) OVER(PARTITION BY deptno) FROM emp e;
MySQL:
SELECT A.*, ROUND(CAST(IF(@DEPTNO = DEPTNO, @MAX := @MAX, @MAX := SUMOVER) AS CHAR ),0) AS SUMOVER2, @DEPTNO := DEPTNO AS VAR2FROM (SELECT E.*, IF(@DEPTNO = DEPTNO, @SUM := @SUM + SAL, @SUM := SAL) AS SUMOVER, @DEPTNO := DEPTNO AS VAR1 FROM emp E, (SELECT @DEPTNO := '', @SUM := 0, @MAX := 0) C ORDER BY DEPTNO) AORDER BY DEPTNO, SUMOVER DESC;
子查询的功能实现如下:
下面是这个语句的结果
首先我们拿第二名的,用Oracle很好实现,不论是第一还是第二。
SELECT *FROM (SELECT E.*, ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RN FROM EMP E)WHERE RN = 2;
Mysql中第一这么实现:
在5.6版本,sql_mode非only_full_group_by的情况,我们可以使用如下方式实现
set global sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SELECT * FROM (SELECT e.* FROM emp e ORDER BY deptno,sal ) a GROUP BY deptno;
在SQL_MODE非only_full_group_by时,MySQL中的group by是只取第一行的,下面我们看取第二行的SQL。
SELECT *FROM (SELECT E.*, IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS RN, @DEPTNO := DEPTNO FROM EMP E, (SELECT @RN := 0, @DEPTNO := 0) C ORDER BY DEPTNO, SAL DESC) XWHERE X.RN = 2;
dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都一样。
SELECT empno,ename,sal,deptno,rank() OVER(PARTITION BY deptno ORDER BY sal desc) as rank,dense_rank() OVER(PARTITION BY deptno ORDER BY sal desc) as dense_rankFROM emp e;
MySQL的写法:
select empno,ename,sal,deptno, if(@deptno = deptno,if(@sal=sal,@rn:=@rn,@rn3:=@rn3+1),@rn:=1) as "RANK() OVER", if(@sal =sal,@rn2:=@rn2 ,if(@deptno = deptno,@rn2:=@rn2+1,@rn2:=1)) as "DENSE_RANK() OVER", if(@deptno = deptno,@rn:=@rn+1,@rn:=1) as "ROW_NUMBER() OVER" , @deptno:=deptno,@sal:=sal from (select empno,ename,sal,deptno from emp a ,(select @rn:=1,@deptno:=0,@rn2:=0,@rn3:=0,@sal:=0,@i:=0) b order by deptno,sal desc) c;
--请写出一条SQL语句,查询出在此期间连续获得冠军的有哪些,其连续的年份的起止时间是多少,结果如下:
create table nba as SELECT '公牛' AS TEAM, '1991' AS Y FROM DUAL UNION ALLSELECT '公牛' AS TEAM, '1992' AS Y FROM DUAL UNION ALLSELECT '公牛' AS TEAM, '1993' AS Y FROM DUAL UNION ALLSELECT '活塞' AS TEAM, '1990' AS Y FROM DUAL UNION ALLSELECT '火箭' AS TEAM, '1994' AS Y FROM DUAL UNION ALLSELECT '火箭' AS TEAM, '1995' AS Y FROM DUAL UNION ALLSELECT '公牛' AS TEAM, '1996' AS Y FROM DUAL UNION ALLSELECT '公牛' AS TEAM, '1997' AS Y FROM DUAL UNION ALLSELECT '公牛' AS TEAM, '1998' AS Y FROM DUAL UNION ALLSELECT '马刺' AS TEAM, '1999' AS Y FROM DUAL UNION ALLSELECT '湖人' AS TEAM, '2000' AS Y FROM DUAL UNION ALLSELECT '湖人' AS TEAM, '2001' AS Y FROM DUAL UNION ALLSELECT '湖人' AS TEAM, '2002' AS Y FROM DUAL UNION ALLSELECT '马刺' AS TEAM, '2003' AS Y FROM DUAL UNION ALLSELECT '活塞' AS TEAM, '2004' AS Y FROM DUAL UNION ALLSELECT '马刺' AS TEAM, '2005' AS Y FROM DUAL UNION ALLSELECT '热火' AS TEAM, '2006' AS Y FROM DUAL UNION ALLSELECT '马刺' AS TEAM, '2007' AS Y FROM DUAL UNION ALLSELECT '凯尔特人' AS TEAM, '2008' AS Y FROM DUAL UNION ALLSELECT '湖人' AS TEAM, '2009' AS Y FROM DUAL UNION ALLSELECT '湖人' AS TEAM, '2010' AS Y FROM DUAL;
Oracle实现:
SELECT TEAM, MIN(Y), MAX(Y)FROM (SELECT E.*, ROWNUM, ROW_NUMBER() OVER(PARTITION BY TEAM ORDER BY Y) AS RN, ROWNUM - ROW_NUMBER() OVER(PARTITION BY TEAM ORDER BY Y) AS DIFF FROM NBA E ORDER BY Y)GROUP BY TEAM, DIFFHAVING MIN(Y) != MAX(Y)ORDER BY 2;
MySQL实现:
SELECT TEAM, MIN(Y), MAX(Y)FROM (SELECT TEAM, Y, IF(@TEAM = TEAM, @RN := @RN + 1, @RN := 1) AS RWN, @RN1 := @RN1 + 1 AS RN, @TEAM := TEAM FROM nba N, (SELECT @RN := 0, @TEAM := '', @RN1 := '') C) AGROUP BY RN - RWNHAVING MIN(Y) != MAX(Y)ORDER BY 2
Userdefined Function,用户定义函数。我们知道,MySQL本身支持很多内建的函数,此外还可以通过创建存储方法来定义函数。UDF为用户提供了一种更高效的方式来创建函数。
UDF与普通函数类似,有参数,也有输出。分为两种类型:单次调用型和聚集函数。前者能够针对每一行数据进行处理,后者则用于处理Group By这样的情况。
UDF自定义函数,在MySQL basedir/include
[root@test12c include]# pwd/usr/local/mysql/include[root@test12c include]# cat rownum.c #include#include #if defined(MYSQL_SERVER)#include /* To get strmov() */#else/* when compiled as standalone */#include #define strmov(a,b) stpcpy(a,b)#endif#include #include /*gcc -fPIC -Wall -I/usr/local/mysql/include -I. -shared rownum.c -o rownum.soDROP FUNCTION IF EXISTS rownum;CREATE FUNCTION rownum RETURNS INTEGER SONAME 'rownum.so';*/C_MODE_START;my_bool rownum_init(UDF_INIT *initid, UDF_ARGS *args, char *message);void rownum_deinit(UDF_INIT *initid);chong rownum(UDF_INIT *initid, UDF_ARGS *args, char *is_null,char *error);C_MODE_END;/*Simple example of how to get a sequences starting from the first argumentor 1 if no arguments have been given*/my_bool rownum_init(UDF_INIT *initid, UDF_ARGS *args, char *message){if (args->arg_count > 1){strmov(message,"This function takes none or 1 argument");return 1;}if (args->arg_count)args->arg_type[0]= INT_RESULT; /* Force argument to int */if (!(initid->ptr=(char*) malloc(sizeof(chong)))){strmov(message,"Couldn't allocate memory");return 1;}memset(initid->ptr, 0, sizeof(chong));initid->const_item=0;return 0;}void rownum_deinit(UDF_INIT *initid){if (initid->ptr)free(initid->ptr);}chong rownum(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,char *is_null __attribute__((unused)),char *error __attribute__((unused))){uchong val=0;if (args->arg_count)val= *((chong*) args->args[0]);return ++*((chong*) initid->ptr) + val;}
生成动态链接库
gcc rownum.c -fPIC -shared -o ../lib/plugin/rownum.so
| 作者简介
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28218939/viewspace-2217845/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28218939/viewspace-2217845/