本篇文章为大家展示了MySQL中怎么实现一个分析函数，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

1. 实现rownum

1

2

SET@rn:=0;

SELECT@rn:=@rn+1ASrownum,e.*FROMempe;

或者写成：

1

SELECT@rn:=@rn+1ASrownum,e.*FROMempe,(SELECT@rn:=0)c

2. 各种分析函数写法 (MySQL实现分析语句时可能遇到的各种计算问题)2.1 sum() 实现

--SQL 执行顺序 ,FROM ,JOIN ,WHERE ,GROUP BY,HAVING ,ORDER BY ,SELECT,

在Oracle中分页语句的原始语句如下：

1

SELECTE.*,SUM(SAL)OVER(PARTITIONBYDEPTNO)ASCOUNTOVERFROMEMPE;

1

2

3

4

5

6

7

8

SELECTE.*,

(SELECTSUMOVER

FROM(SELECTDEPTNO,SUM(SAL)ASSUMOVER

FROMEMPE1

GROUPBYDEPTNO)X

WHEREX.DEPTNO=E.DEPTNO)ASCOUNTOVER

FROMEMPE

ORDERBYDEPTNO;

Mysql中也是这么实现的：

1

2

3

4

5

6

7

8

SELECTE.*,

(SELECTSUMOVER

FROM(SELECTDEPTNO,SUM(SAL)ASSUMOVER

FROMempE1

GROUPBYDEPTNO)X

WHEREX.DEPTNO=E.DEPTNO)ASCOUNTOVER

FROMempE

ORDERBYDEPTNO;


2.2 row_number () 实现

1

2

select

e.*,row_number()over(partitionbydeptnoorderbyempno)asROW_NUMBERfromempe;

我们的默认规则是在from后初始化变量。

1

2

3

4

5

SELECTE.*,

IF(@DEPTNO=DEPTNO,@RN:=@RN+1,@RN:=1)ASROW_NUMBER,

@DEPTNO:=DEPTNOASVAR1

FROMEMPE,(SELECT@DEPTNO:='',@RN:=0)C

ORDERBYDEPTNO;

1

2

3

4

5

SELECTE.*,

IF(@DEPTNO=DEPTNO,@RN:=@RN+1,@RN:=1)ASROW_NUMBER,

@DEPTNO:=DEPTNOASVAR1

FROMEMPE,(SELECT@DEPTNO:='',@RN:=0)C

ORDERBYDEPTNO;

这个语句首先执行order by

2.3 求每个人员占他所在部门总工资的百分比

在Oracle中实现：

1

2

3

4

SELECTE.*,

TRUNC(SAL/SUM(SAL)OVER(PARTITIONBYDEPTNO),3)ASSALPERCENT

FROMEMPE

ORDERBYDEPTNO;

1

2

3

4

5

6

7

8

SELECTE.*,

SAL/(SELECTSUMOVER

FROM(SELECTDEPTNO,SUM(SAL)ASSUMOVER

FROMempE1

GROUPBYDEPTNO)X

WHEREX.DEPTNO=E.DEPTNO)ASSalPercent

FROMempE

ORDERBYDEPTNO;

2.4 求各个部门的总共工资

Oracle:

1

SELECTe.*,SUM(sal)OVER(PARTITIONBYdeptno)FROMempe;

MySQL:

1

2

3

4

5

6

7

8

9

SELECTA.*,

ROUND(CAST(IF(@DEPTNO=DEPTNO,@MAX:=@MAX,@MAX:=SUMOVER)ASCHAR),0)ASSUMOVER2,

@DEPTNO:=DEPTNOASVAR2

FROM(SELECTE.*,

IF(@DEPTNO=DEPTNO,@SUM:=@SUM+SAL,@SUM:=SAL)ASSUMOVER,

@DEPTNO:=DEPTNOASVAR1

FROMempE,(SELECT@DEPTNO:='',@SUM:=0,@MAX:=0)C

ORDERBYDEPTNO)A

ORDERBYDEPTNO,SUMOVERDESC;

子查询的功能实现如下：

下面是这个语句的结果

2.5 拿部门第二的工资的人

首先我们拿第二名的，用Oracle很好实现，不论是第一还是第二。

1

2

3

4

5

SELECT*

FROM(SELECTE.*,

ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYSALDESC)ASRN

FROMEMPE)

WHERERN=2;

Mysql中第一这么实现：

在5.6版本，sql_mode非only_full_group_by的情况，我们可以使用如下方式实现

1

setglobalsql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

1

SELECT*FROM(SELECTe.*FROMempeORDERBYdeptno,sal)aGROUPBYdeptno;

在SQL_MODE非only_full_group_by时，MySQL中的group by是只取第一行的，下面我们看取第二行的SQL。

1

2

3

4

5

6

7

SELECT*

FROM(SELECTE.*,

IF(@DEPTNO=DEPTNO,@RN:=@RN+1,@RN:=1)ASRN,

@DEPTNO:=DEPTNO

FROMEMPE,(SELECT@RN:=0,@DEPTNO:=0)C

ORDERBYDEPTNO,SALDESC)X

WHEREX.RN=2;


2.6 dense_rank()

dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都一样。

1

2

3

4

5

6

7

SELECTempno,

ename,

sal,

deptno,

rank()OVER(PARTITIONBYdeptnoORDERBYsaldesc)asrank,

dense_rank()OVER(PARTITIONBYdeptnoORDERBYsaldesc)asdense_rank

FROMempe;


MySQL的写法：

1

2

3

4

5

6

7

8

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

(selectempno,ename,sal,deptnofromempa,(select@rn:=1,@deptno:=0,@rn2:=0,@rn3:=0,@sal:=0,@i:=0)borderbydeptno,saldesc)c;


2.7 连续获得冠军的有哪些

--请写出一条SQL语句，查询出在此期间连续获得冠军的有哪些，其连续的年份的起止时间是多少，结果如下：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

createtablenbaas

SELECT'公牛'ASTEAM,'1991'ASYFROMDUALUNIONALL

SELECT'公牛'ASTEAM,'1992'ASYFROMDUALUNIONALL

SELECT'公牛'ASTEAM,'1993'ASYFROMDUALUNIONALL

SELECT'活塞'ASTEAM,'1990'ASYFROMDUALUNIONALL

SELECT'火箭'ASTEAM,'1994'ASYFROMDUALUNIONALL

SELECT'火箭'ASTEAM,'1995'ASYFROMDUALUNIONALL

SELECT'公牛'ASTEAM,'1996'ASYFROMDUALUNIONALL

SELECT'公牛'ASTEAM,'1997'ASYFROMDUALUNIONALL

SELECT'公牛'ASTEAM,'1998'ASYFROMDUALUNIONALL

SELECT'马刺'ASTEAM,'1999'ASYFROMDUALUNIONALL

SELECT'湖人'ASTEAM,'2000'ASYFROMDUALUNIONALL

SELECT'湖人'ASTEAM,'2001'ASYFROMDUALUNIONALL

SELECT'湖人'ASTEAM,'2002'ASYFROMDUALUNIONALL

SELECT'马刺'ASTEAM,'2003'ASYFROMDUALUNIONALL

SELECT'活塞'ASTEAM,'2004'ASYFROMDUALUNIONALL

SELECT'马刺'ASTEAM,'2005'ASYFROMDUALUNIONALL

SELECT'热火'ASTEAM,'2006'ASYFROMDUALUNIONALL

SELECT'马刺'ASTEAM,'2007'ASYFROMDUALUNIONALL

SELECT'凯尔特人'ASTEAM,'2008'ASYFROMDUALUNIONALL

SELECT'湖人'ASTEAM,'2009'ASYFROMDUALUNIONALL

SELECT'湖人'ASTEAM,'2010'ASYFROMDUAL;

Oracle实现：

1

2

3

4

5

6

7

8

9

10

SELECTTEAM,MIN(Y),MAX(Y)

FROM(SELECTE.*,

ROWNUM,

ROW_NUMBER()OVER(PARTITIONBYTEAMORDERBYY)ASRN,

ROWNUM-ROW_NUMBER()OVER(PARTITIONBYTEAMORDERBYY)ASDIFF

FROMNBAE

ORDERBYY)

GROUPBYTEAM,DIFF

HAVINGMIN(Y)!=MAX(Y)

ORDERBY2;

MySQL实现：

1

2

3

4

5

6

7

8

9

10

SELECTTEAM,MIN(Y),MAX(Y)

FROM(SELECTTEAM,

Y,

IF(@TEAM=TEAM,@RN:=@RN+1,@RN:=1)ASRWN,

@RN1:=@RN1+1ASRN,

@TEAM:=TEAM

FROMnbaN,(SELECT@RN:=0,@TEAM:='',@RN1:='')C)A

GROUPBYRN-RWN

HAVINGMIN(Y)!=MAX(Y)

ORDERBY2

| UDF插件

Userdefined Function，用户定义函数。我们知道，MySQL本身支持很多内建的函数，此外还可以通过创建存储方法来定义函数。UDF为用户提供了一种更高效的方式来创建函数。

UDF与普通函数类似，有参数，也有输出。分为两种类型：单次调用型和聚集函数。前者能够针对每一行数据进行处理，后者则用于处理Group By这样的情况。

UDF自定义函数，在MySQL basedir/include

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

[root@test12cinclude]#pwd

/usr/local/mysql/include

[root@test12cinclude]#catrownum.c

#include<my_global.h>

#include<my_sys.h>

#ifdefined(MYSQL_SERVER)

#include<m_string.h>/*Togetstrmov()*/

#else

/*whencompiledasstandalone*/

#include<string.h>

#definestrmov(a,b)stpcpy(a,b)

#endif

#include<mysql.h>

#include<ctype.h>

/*

gcc-fPIC-Wall-I/usr/local/mysql/include-I.-sharedrownum.c-orownum.so

DROPFUNCTIONIFEXISTSrownum;

CREATEFUNCTIONrownumRETURNSINTEGERSONAME'rownum.so';

*/

C_MODE_START;

my_boolrownum_init(UDF_INIT*initid,UDF_ARGS*args,char*message);

voidrownum_deinit(UDF_INIT*initid);

chongrownum(UDF_INIT*initid,UDF_ARGS*args,char*is_null,char*error);

C_MODE_END;

/*

Simpleexampleofhowtogetasequencesstartingfromthefirstargument

or1ifnoargumentshavebeengiven

*/

my_boolrownum_init(UDF_INIT*initid,UDF_ARGS*args,char*message)

{

if(args->arg_count>1)

{

strmov(message,"Thisfunctiontakesnoneor1argument");

return1;

}

if(args->arg_count)

args->arg_type[0]=INT_RESULT;/*Forceargumenttoint*/

if(!(initid->ptr=(char*)malloc(sizeof(chong))))

{

strmov(message,"Couldn'tallocatememory");

return1;

}

memset(initid->ptr,0,sizeof(chong));

initid->const_item=0;

return0;

}

voidrownum_deinit(UDF_INIT*initid)

{

if(initid->ptr)

free(initid->ptr);

}

chongrownum(UDF_INIT*initid__attribute__((unused)),UDF_ARGS*args,char*is_null__attribute__((unused)),char*error__attribute__((unused)))

{

uchongval=0;

if(args->arg_count)

val=*((chong*)args->args[0]);

return++*((chong*)initid->ptr)+val;

}

生成动态链接库

1

gccrownum.c-fPIC-shared-o../lib/plugin/rownum.so

上述内容就是MySQL中怎么实现一个分析函数，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。