mysql高级查询语句3

本文主要是介绍mysql高级查询语句3，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

sql 高级查询语句3

一计算排名，中位数，累加总值，占比百分比

1.1 算排名

表格自我连接（self join），依次比较比自己大（或者小）以及和各个表字段值一样的，一共有几个个

然后将结果依序列出，算出每一行之前（包括那一行本身）有多少行数.

select  A1.name,A1.score,count(A1.score) rank from class2  A1,class2  A2
     where A1.score <A2.score or (A1.score=A2.score and A1.name=A2.name)
     group by A1.name order by A1.score  desc;
     

+-------------+-------+------+
| name        | score | rank |
+-------------+-------+------+
| wanglei     |   100 |    1 |
| zhoujiazhen |    90 |    2 |
| houlu       |    90 |    2 |
| zhangsan    |    80 |    4 |
| lisi        |    70 |    5 |
| lirui       |    70 |    5 |
| wangwu      |    60 |    7 |
+-------------+-------+------+
7 rows in set (0.00 sec)






select  A1.name,A1.score,count(A1.score) rank from class2  A1,class2  A2
     where A1.score <A2.score or (A1.score=A2.score and A1.name>=A2.name)
     group by A1.name order by  rank  ;
     
 +-------------+-------+------+
| name        | score | rank |
+-------------+-------+------+
| wanglei     |   100 |    1 |
| houlu       |    90 |    2 |
| zhoujiazhen |    90 |    3 |
| zhangsan    |    80 |    4 |
| lirui       |    70 |    5 |
| lisi        |    70 |    6 |
| wangwu      |    60 |    7 |
+-------------+-------+------+
7 rows in set (0.00 sec)

1.2 计算中位数

1.2.1借助view视图，保存查询结果，再计算中位数

#先将算排名的结果保存为视图
mysql> create view V_Middle as  
    -> select A1.name,A1.score,count(A1.score) rank from class2 A1,class2 A2 
    -> where A1.score<A2.score or (A1.score=A2.score and A1.name=A2.name)
    -> group by A1.name,A1.score order by A1.score desc;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from V_Middle;
+-------------+-------+------+
| name        | score | rank |
+-------------+-------+------+
| wanglei     |   100 |    1 |
| zhoujiazhen |    90 |    2 |
| houlu       |    90 |    2 |
| zhangsan    |    80 |    4 |
| lisi        |    70 |    5 |
| lirui       |    70 |    5 |
| wangwu      |    60 |    7 |
+-------------+-------+------+
7 rows in set (0.00 sec)


#再根据排名结果计算中位数（中位数计算就是总数加1再除以2）
mysql> select name,score Middle from V_Middle 
    -> where rank= (select (count(*)+1) div 2 from class2);
+----------+--------+
| name     | Middle |
+----------+--------+
| zhangsan |     80 |
+----------+--------+
1 row in set (0.00 sec)

DIV是在MySQL中算出商的方式

1.2.2 使用派生表计算中位数

select name,score Middle from 
   (select A1.name,A1.score,count(A1.score) rank from class2 A1,class2 A2 
       where A1.score<A2.score or (A1.score=A2.score and A1.name=A2.name)
        group by A1.name,A1.score order by A1.score desc) A3
   where A3.rank=(select (count(*)+1) div 2 from class2);
    
+----------+--------+
| name     | Middle |
+----------+--------+
| zhangsan |     80 |
+----------+--------+

1.3 计算累计总值

表格自我连接（self join），然后将结果依序列出，算出每一行之前（包括那一行本身）的总和

select  A1.name,A1.score,sum(A2.score) sum,count(A2.score)  rank 
     from class2  A1,class2  A2
     where A1.score <A2.score or (A1.score=A2.score and A1.name=A2.name)
     group by A1.name order by  rank ;
 
 +-------------+-------+------+------+
| name        | score | sum  | rank |
+-------------+-------+------+------+
| wanglei     |   100 |  100 |    1 |
| houlu       |    90 |  190 |    2 |
| zhoujiazhen |    90 |  190 |    2 |
| zhangsan    |    80 |  360 |    4 |
| lirui       |    70 |  430 |    5 |
| lisi        |    70 |  430 |    5 |
| wangwu      |    60 |  560 |    7 |
+-------------+-------+------+------+
7 rows in set (0.00 sec)

1.4 算占总百分比

select  A1.name,A1.score,
     A1.score/(select sum(score) from class2),
     count(A2.score)  rank 
     from class2  A1,class2  A2
     where A1.score <A2.score or (A1.score=A2.score and A1.name=A2.name)
     group by A1.name order by  rank ;
 
 +-------------+-------+------------------------------------------+------+
| name        | score | A1.score/(select sum(score) from class2) | rank |
+-------------+-------+------------------------------------------+------+
| wanglei     |   100 |                                   0.1786 |    1 |
| houlu       |    90 |                                   0.1607 |    2 |
| zhoujiazhen |    90 |                                   0.1607 |    2 |
| zhangsan    |    80 |                                   0.1429 |    4 |
| lisi        |    70 |                                   0.1250 |    5 |
| lirui       |    70 |                                   0.1250 |    5 |
| wangwu      |    60 |                                   0.1071 |    7 |
+-------------+-------+------------------------------------------+------+
7 rows in set (0.00 sec)



select  A1.name,A1.score, 
     round(A1.score/(select sum(score) from class2) *100,2)  ||  '%'  percent,
     count(A2.score)  rank 
     from class2  A1,class2  A2
     where A1.score <A2.score or (A1.score=A2.score and A1.name>=A2.name)
     group by A1.name order by  rank ;
    
+-------------+-------+---------+------+
| name        | score | percent | rank |
+-------------+-------+---------+------+
| wanglei     |   100 | 17.86%  |    1 |
| houlu       |    90 | 16.07%  |    2 |
| zhoujiazhen |    90 | 16.07%  |    2 |
| zhangsan    |    80 | 14.29%  |    4 |
| lisi        |    70 | 12.50%  |    5 |
| lirui       |    70 | 12.50%  |    5 |
| wangwu      |    60 | 10.71%  |    7 |
+-------------+-------+---------+------+

1.5 计算累加值百分比

select  A1.name,A1.score, 
     round( sum(A2.score)/(select sum(score) from class2) *100,2)  ||  '%'  percent,
     count(A2.score)  rank 
     from class2  A1,class2  A2
     where A1.score <A2.score or (A1.score=A2.score and A1.name>=A2.name)
     group by A1.name order by  rank;
     
+-------------+-------+---------+------+
| name        | score | percent | rank |
+-------------+-------+---------+------+
| wanglei     |   100 | 17.86%  |    1 |
| houlu       |    90 | 33.93%  |    2 |
| zhoujiazhen |    90 | 50.00%  |    3 |
| zhangsan    |    80 | 64.29%  |    4 |
| lirui       |    70 | 76.79%  |    5 |
| lisi        |    70 | 89.29%  |    6 |
| wangwu      |    60 | 100.00% |    7 |
+-------------+-------+---------+------+

二存储过程

2.1 存储过程概述

2.1.1 什么是存储过程

存储过程是一组为了完成特定功能的SQL语句集合
存储过程在使用过程中是将常用或者复杂的工作预先使用 SQL 语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中，当需要使用时，只需要调用即可
存储过程在执行上比传统SQL速度更快、执行效率更高。

2.1.1、优点

执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
SQL语句加上控制语句的集合，灵活性高
在服务器端存储，客户端调用时，降低网络负载
可多次重复被调用，可随时修改，不影响客户端调用
可完成所有的数据库操作，也可控制数据库的信息访问权限

2.2 操作存储过程

2.2.1 创建存储过程

DELIMITER   ##               #将语句的结束符号从分号;临时改为两个$$ (可以是自定义)
CREATE PROCEDURE proc()      #创建存储过程，过程名为Proc， 不带参数
-> BEGIN                      #过程体以关键字BEGIN开始
-> SELECT * FROM test5;       #过程体语句（自己根据需求进行编写）
-> END  ##                    #过程体以关键字END结束
DELIMITER ;                   #将语句的结束符号恢复为分号



eg：
mysql> delimiter $$
mysql> create procedure proc()
    -> begin
    -> select * from school.class2;
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

2.2.2 调用存储过程

#调用存储过程，call 存储过程名
call proc;

2.2.3 查看存储过程

 #查看某个存储过程的具体信息（如果在指定库中，库名可以省略）
SHOW CREATE PROCEDURE [数据库.] 存储过程名;        

#查看存储过程装态
SHOW PROCEDURE STATUS [LIKE '%proc%'] \G

2.2.4存储过程的参数

IN输入参数

表示调用者向过程传入值(传入值可以是字面量或变量)

OUT输出参数

表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)

INOUT输入输出参数

既表示调用者向过程传入值，又表示过程向调用者传出值(值只能是变量)

mysql> delimiter $$
mysql> create procedure proc2(in inname char(20))
    -> begin 
    -> select * from school.class2 where name=inname;
    -> end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> 
mysql> call proc2('wanglei');
+---------+-------+
| name    | score |
+---------+-------+
| wanglei |   100 |
+---------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

2.2.5 删除存储过程

#删除school库里的存储过程proc。 if exists ，仅在存在时执行，
drop  procedure  if exists school.proc;

2.3 存储过程的控制语句

2.3.1 条件控制语句 if-then-else ....end if

mysql> delimiter $$
mysql> create procedure proc_num(in  num int)
    -> begin
    -> declare bianliang int;         #declare ，设置变量
    -> set bianliang=num *2;
    -> if bianliang >15 then
    -> update school.num set id=id+1;
    -> else
    -> update school.num set id=id-5;
    -> end if;              #end if 结束if语句
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;


#调用对等体，并传入参数
call proc_num(6)
call proc_num(10);

2.3.2 循环语句 while .....end while

mysql> delimiter $$
mysql> create procedure proc_num2()
    -> begin 
    -> declare bianliang int;
    -> set bianliang=0;
    -> while  bianliang<10 do
    -> update school.num set id=id+1;
    -> set  bianliang=bianliang+1;
    -> end while;        #end while 结束while 循环
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

call proc_num2;

这篇关于mysql高级查询语句3的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySql教程

mysql高级查询语句3

一计算排名，中位数，累加总值，占比百分比

1.1 算排名

1.2 计算中位数

1.2.1借助view视图，保存查询结果，再计算中位数

1.2.2 使用派生表计算中位数

1.3 计算累计总值

1.4 算占总百分比

1.5 计算累加值百分比

二存储过程

2.1 存储过程概述

2.1.1 什么是存储过程

2.1.1、优点

2.2 操作存储过程

2.2.1 创建存储过程

2.2.2 调用存储过程

2.2.3 查看存储过程

2.2.4存储过程的参数

2.2.5 删除存储过程

2.3 存储过程的控制语句

2.3.1 条件控制语句 if-then-else ....end if

2.3.2 循环语句 while .....end while

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯

mysql高级查询语句3

一计算排名，中位数，累加总值，占比百分比

1.1 算 排名

1.2 计算中位数

1.2.1借助view视图，保存查询结果，再计算中位数

1.2.2 使用派生表计算中位数

1.3 计算累计总值

1.4 算占总百分比

1.5 计算累加值百分比

二 存储过程

2.1 存储过程概述

2.1.1 什么是存储过程

2.1.1、优点

2.2 操作存储过程

2.2.1 创建存储过程

2.2.2 调用存储过程

2.2.3 查看存储过程

2.2.4存储过程的参数

2.2.5 删除存储过程

2.3 存储过程的控制语句

2.3.1 条件控制语句 if-then-else ....end if

2.3.2 循环语句 while .....end while

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯

1.1 算排名

二存储过程