共计 7022 个字符，预计需要花费 18 分钟才能阅读完成。
自动写代码机器人，免费开通
本篇文章为大家展示了 MySQL 中怎么实现一个分析函数，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。
1. 实现 rownum
1
2
SET @rn:=0;
SELECT @rn:=@rn+1 AS rownum ,e.* FROM emp e;
或者写成：
1
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 中分页语句的原始语句如下：
1
SELECT E.*, SUM(SAL) OVER(PARTITION BY DEPTNO) AS COUNTOVER FROM EMP E;
1
2
3
4
5
6
7
8
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 中也是这么实现的：
1
2
3
4
5
6
7
8
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 () 实现
1
2
select
e.* ,row_number() over(partition by deptno order by empno) as ROW_NUMBER from emp e;
我们的默认规则是在 from 后初始化变量。
1
2
3
4
5
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;
1
2
3
4
5
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;
这个语句首先执行 order by
2.3 求每个人员占他所在部门总工资的百分比
在 Oracle 中实现：
1
2
3
4
SELECT E.*,
TRUNC(SAL / SUM(SAL) OVER(PARTITION BY DEPTNO), 3) AS SALPERCENT
FROM EMP E
ORDER BY DEPTNO;
1
2
3
4
5
6
7
8
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 SalPercent
FROM emp E
ORDER BY DEPTNO;
2.4 求各个部门的总共工资
Oracle:
1
SELECT e.* ,SUM(sal) OVER(PARTITION BY deptno) FROM emp e;
MySQL:
1
2
3
4
5
6
7
8
9
SELECT A.*,
ROUND(CAST(IF(@DEPTNO = DEPTNO, @MAX := @MAX, @MAX := SUMOVER) AS CHAR ),0) AS SUMOVER2,
@DEPTNO := DEPTNO AS VAR2
FROM (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) A
ORDER BY DEPTNO, SUMOVER DESC;
子查询的功能实现如下：
下面是这个语句的结果
2.5 拿部门第二的工资的人
首先我们拿第二名的，用 Oracle 很好实现，不论是第一还是第二。
1
2
3
4
5
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 的情况，我们可以使用如下方式实现
1
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 ;
1
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。
1
2
3
4
5
6
7
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) X
WHERE X.RN = 2;
2.6 dense_rank()
dense_rank 函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都一样。
1
2
3
4
5
6
7
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_rank
FROM emp e;
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
(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;
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
create table nba as
SELECT 公牛 AS TEAM, 1991 AS Y FROM DUAL UNION ALL
SELECT 公牛 AS TEAM, 1992 AS Y FROM DUAL UNION ALL
SELECT 公牛 AS TEAM, 1993 AS Y FROM DUAL UNION ALL
SELECT 活塞 AS TEAM, 1990 AS Y FROM DUAL UNION ALL
SELECT 火箭 AS TEAM, 1994 AS Y FROM DUAL UNION ALL
SELECT 火箭 AS TEAM, 1995 AS Y FROM DUAL UNION ALL
SELECT 公牛 AS TEAM, 1996 AS Y FROM DUAL UNION ALL
SELECT 公牛 AS TEAM, 1997 AS Y FROM DUAL UNION ALL
SELECT 公牛 AS TEAM, 1998 AS Y FROM DUAL UNION ALL
SELECT 马刺 AS TEAM, 1999 AS Y FROM DUAL UNION ALL
SELECT 湖人 AS TEAM, 2000 AS Y FROM DUAL UNION ALL
SELECT 湖人 AS TEAM, 2001 AS Y FROM DUAL UNION ALL
SELECT 湖人 AS TEAM, 2002 AS Y FROM DUAL UNION ALL
SELECT 马刺 AS TEAM, 2003 AS Y FROM DUAL UNION ALL
SELECT 活塞 AS TEAM, 2004 AS Y FROM DUAL UNION ALL
SELECT 马刺 AS TEAM, 2005 AS Y FROM DUAL UNION ALL
SELECT 热火 AS TEAM, 2006 AS Y FROM DUAL UNION ALL
SELECT 马刺 AS TEAM, 2007 AS Y FROM DUAL UNION ALL
SELECT 凯尔特人 AS TEAM, 2008 AS Y FROM DUAL UNION ALL
SELECT 湖人 AS TEAM, 2009 AS Y FROM DUAL UNION ALL
SELECT 湖人 AS TEAM, 2010 AS Y FROM DUAL;
Oracle 实现：
1
2
3
4
5
6
7
8
9
10
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, DIFF
HAVING MIN(Y) != MAX(Y)
ORDER BY 2;
MySQL 实现：
1
2
3
4
5
6
7
8
9
10
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) A
GROUP BY RN – RWN
HAVING MIN(Y) != MAX(Y)
ORDER BY 2
| 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@test12c include]# pwd
/usr/local/mysql/include
[root@test12c include]# cat rownum.c
#include my_global.h
#include my_sys.h
#if defined(MYSQL_SERVER)
#include m_string.h /* To get strmov() */
#else
/* when compiled as standalone */
#include string.h
#define strmov(a,b) stpcpy(a,b)
#endif
#include mysql.h
#include ctype.h
/*
gcc -fPIC -Wall -I/usr/local/mysql/include -I. -shared rownum.c -o rownum.so
DROP 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 argument
or 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;
}
生成动态链接库
1
gcc rownum.c -fPIC -shared -o ../lib/plugin/rownum.so
上述内容就是 MySQL 中怎么实现一个分析函数，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。
向 AI 问一下细节
