MySQL实现显示百分比显示和前百分之几的方法

目录

• 要求
• 实现代码
• 数据库

前几天一个朋友让我帮忙写的，随手记录一下，感觉难度也不大，就是写的时候遇到一些问题。优化方便做得不太好。有好的优化方法欢迎分享！（数据库在文章结尾）

要求

1）查询所有时间内，所有产品销售金额占比，按占比大小降序排序，筛选累计占比在前80%的产品，结果输出排名产品名称销售金额占比累计占比。

2）查询所有时间内，各个国家的销售情况，销售合计金额大于10000视为业绩合格,

否则为不合格，结果输出国家销售金额业绩情况。

3）查询中国、英国每个月份的销售情况，2020年8月份销售合计金额大于10000视为业绩合格，否则为不合格，2020年9月份销售合计金额大于12000视为业绩合格，否则为不合格，结果输出月份中国销售业绩、英国销售业绩。

实现代码

1）

?

1 2 3 4

select a.productid 产品id,(a.sale_amount * b.price) 销售金额,concat((a.sale_amount * b.price / ( select sum (aa.sale_amount * bb.price) m from 2002a aa left join 2002b bb on aa.productid = bb.productid)) * 100, "%" ) percent from ( select @rownum:=0) r,2002a a,2002b b where (@rownum:=@rownum+1)<=( select round( count ( distinct a.productid)*0.8) from 2002a a, 2002b b where a.productid = b.productid) and a.productid = b.productid group by a.productid order by (a.sale_amount * b.price) desc ;

2）

?

1 2	select country 国家, sum (price*sale_amount) 销售金额,if( sum (price*sale_amount)>10000, '合格' , '不合格' ) 业绩情况 from 2002a a,2002b b,2002c c where a.productid=b.productid and a.customid=c.customid group by country;

3）

?

1 2 3 4 5

select date_format(ztime, '%y-%m' ) 月份, sum (price*sale_amount) 销售金额, if((date_format(ztime, '%y-%m' )= '2020-08' and sum (price*sale_amount)>10000) or (date_format(ztime, '%y-%m' )= '2020-09' and sum (price*sale_amount)>13000) and country= '中国' , '合格' , '不合格' ) 中国销售业绩, if((date_format(ztime, '%y-%m' )= '2020-08' or sum (price*sale_amount)>10000) and (date_format(ztime, '%y-%m' )= '2020-09' and sum (price*sale_amount)>13000) and country= '英国' , '合格' , '不合格' ) 英国销售业绩 from 2002a a,2002b b,2002c c where a.productid=b.productid and a.customid=c.customid and country in ( '中国' , '英国' ) and (date_format(ztime, '%y-%m' )= '2020-09' or date_format(ztime, '%y-%m' )= '2020-08' ) group by date_format(ztime, '%y-%m' );

实现查询结果显示前百分之八十的方法：

实现百分比显示：

首先认识两个函数concat()和left()、truncate(a,b)

concat(str1,str2,...)拼接字符串，返回来自于参数连结的字符串。如果任何参数是null， 返回null。可以拼接多个。

left(str,length)从左开始截取字符串.说明：left(被截取字段，截取长度)

truncate(a,b)返回被舍去至小数点后b位的数字a。若b的值为0,则结果不带有小数点或不带有小数部分。可以将b设为负数,若要截去(归零)a小数点左起第b位开始后面所有低位的值.，所有数字的舍入方向都接近于零

结合一下（我上面的代码没使用left）：concat ( left (数值1 / 数值2 *100,5),'%') as 投诉率

示例：

?

1 2	select id,concat( truncate (passscore / (danscore+panscore+duoscore) *100,2), '%' ) as 成绩与总分比 from aqsc_kaoshi_record;

实现mysql查询前百分之几的数据（这里是80％）

mysql不支持top和rowid，使用limit的方式也行不通。所以使用下面这种方式：

?

1 2 3

select a.* from ( select @rownum:=0) r,2002a a where (@rownum:=@rownum+1)<=( select round( count (*)*0.8) from 2002a);

这里的rownum只是个变量名，也可以是用其他的

将student表的grade从大到小排序后的前20%案例：

?

1 2 3

select @rownum:=@rownum+1,student.* from ( select @rownum:=0) row ,( select * from student order by student.grade desc ) student ##排序 where @rownum<( select round( count (*)/4) from student)

除了if外实现判断显示的示例：

?

1 2 3 4

select         sum ( case when sex = '男' then 1 else 0 end )   /* 这是求男生人数 */         sum ( case when sex = '女' then 1 else 0 end )   /* 这是求女生人数 */ from student

数据库

以下是数据库完整代码：

?

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 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232

/* navicat mysql data transfer   source server         : first source server version : 80011 source host           : localhost:3306 source database       : fr_test_sql   target server type    : mysql target server version : 80011 file encoding         : 65001   date : 2021-12-18 16:06:19 */   set foreign_key_checks=0;   -- ---------------------------- -- table structure for `2002a` -- ---------------------------- drop table if exists `2002a`; create table `2002a` (    `orderid` varchar (255) not null ,    `ztime` date not null ,    `productid` varchar (255) not null ,    `sale_amount` int (11) not null ,    `customid` varchar (255) not null ,    primary key (`orderid`) ) engine=innodb default charset=utf8;   -- ---------------------------- -- records of 2002a -- ---------------------------- insert into `2002a` values ( 'o001' , '2020-09-10' , 'p010' , '96' , 'c008' ); insert into `2002a` values ( 'o002' , '2020-08-29' , 'p008' , '38' , 'c007' ); insert into `2002a` values ( 'o003' , '2020-08-10' , 'p007' , '97' , 'c008' ); insert into `2002a` values ( 'o004' , '2020-09-27' , 'p005' , '62' , 'c006' ); insert into `2002a` values ( 'o005' , '2020-08-17' , 'p007' , '37' , 'c009' ); insert into `2002a` values ( 'o006' , '2020-09-06' , 'p006' , '3' , 'c005' ); insert into `2002a` values ( 'o007' , '2020-08-30' , 'p009' , '86' , 'c007' ); insert into `2002a` values ( 'o008' , '2020-09-04' , 'p001' , '34' , 'c007' ); insert into `2002a` values ( 'o009' , '2020-09-09' , 'p003' , '99' , 'c004' ); insert into `2002a` values ( 'o010' , '2020-09-06' , 'p002' , '65' , 'c010' ); insert into `2002a` values ( 'o011' , '2020-08-08' , 'p005' , '11' , 'c002' ); insert into `2002a` values ( 'o012' , '2020-09-20' , 'p002' , '3' , 'c008' ); insert into `2002a` values ( 'o013' , '2020-08-15' , 'p004' , '9' , 'c004' ); insert into `2002a` values ( 'o014' , '2020-08-28' , 'p007' , '99' , 'c010' ); insert into `2002a` values ( 'o015' , '2020-08-23' , 'p003' , '3' , 'c005' ); insert into `2002a` values ( 'o016' , '2020-08-08' , 'p006' , '51' , 'c008' ); insert into `2002a` values ( 'o017' , '2020-09-04' , 'p009' , '99' , 'c002' ); insert into `2002a` values ( 'o018' , '2020-08-12' , 'p007' , '86' , 'c003' ); insert into `2002a` values ( 'o019' , '2020-09-22' , 'p001' , '73' , 'c005' ); insert into `2002a` values ( 'o020' , '2020-08-03' , 'p009' , '22' , 'c006' ); insert into `2002a` values ( 'o021' , '2020-08-22' , 'p007' , '54' , 'c006' ); insert into `2002a` values ( 'o022' , '2020-09-29' , 'p005' , '59' , 'c005' ); insert into `2002a` values ( 'o023' , '2020-08-15' , 'p003' , '45' , 'c006' ); insert into `2002a` values ( 'o024' , '2020-09-12' , 'p001' , '10' , 'c004' ); insert into `2002a` values ( 'o025' , '2020-08-23' , 'p004' , '56' , 'c008' ); insert into `2002a` values ( 'o026' , '2020-09-17' , 'p003' , '57' , 'c004' ); insert into `2002a` values ( 'o027' , '2020-08-23' , 'p002' , '73' , 'c003' ); insert into `2002a` values ( 'o028' , '2020-09-22' , 'p003' , '50' , 'c008' ); insert into `2002a` values ( 'o029' , '2020-09-22' , 'p003' , '70' , 'c007' ); insert into `2002a` values ( 'o030' , '2020-08-13' , 'p006' , '15' , 'c002' );   -- ---------------------------- -- table structure for `2002b` -- ---------------------------- drop table if exists `2002b`; create table `2002b` (    `productid` varchar (255) character set utf8 collate utf8_general_ci not null ,    `productname` varchar (255) character set utf8 collate utf8_general_ci not null ,    `price` decimal (10,0) not null ,    primary key (`productid`) ) engine=innodb default charset=utf8;   -- ---------------------------- -- records of 2002b -- ---------------------------- insert into `2002b` values ( 'p001' , '产品a' , '29' ); insert into `2002b` values ( 'p002' , '产品b' , '50' ); insert into `2002b` values ( 'p003' , '产品c' , '42' ); insert into `2002b` values ( 'p004' , '产品d' , '59' ); insert into `2002b` values ( 'p005' , '产品e' , '49' ); insert into `2002b` values ( 'p006' , '产品f' , '10' ); insert into `2002b` values ( 'p007' , '产品g' , '23' ); insert into `2002b` values ( 'p008' , '产品h' , '24' ); insert into `2002b` values ( 'p009' , '产品i' , '50' ); insert into `2002b` values ( 'p010' , '产品j' , '64' );   -- ---------------------------- -- table structure for `2002c` -- ---------------------------- drop table if exists `2002c`; create table `2002c` (    `customid` varchar (255) character set utf8 collate utf8_general_ci not null ,    `customname` varchar (255) not null ,    `country` varchar (255) not null ,    primary key (`customid`) ) engine=innodb default charset=utf8;   -- ---------------------------- -- records of 2002c -- ---------------------------- insert into `2002c` values ( 'c001' , '客户a' , '中国' ); insert into `2002c` values ( 'c002' , '客户b' , '法国' ); insert into `2002c` values ( 'c003' , '客户c' , '中国' ); insert into `2002c` values ( 'c004' , '客户d' , '英国' ); insert into `2002c` values ( 'c005' , '客户e' , '美国' ); insert into `2002c` values ( 'c006' , '客户f' , '中国' ); insert into `2002c` values ( 'c007' , '客户g' , '法国' ); insert into `2002c` values ( 'c008' , '客户h' , '英国' ); insert into `2002c` values ( 'c009' , '客户i' , '美国' ); insert into `2002c` values ( 'c010' , '客户h' , '英国' );   -- ---------------------------- -- table structure for `2003_a` -- ---------------------------- drop table if exists `2003_a`; create table `2003_a` (    `classno` varchar (255) default null ,    `studentno` varchar (255) default null ,    `grade` varchar (255) default null ) engine=innodb default charset=utf8;   -- ---------------------------- -- records of 2003_a -- ---------------------------- insert into `2003_a` values ( 'class1' , '1001' , '86' ); insert into `2003_a` values ( 'class1' , '1002' , '60' ); insert into `2003_a` values ( 'class1' , '1003' , '85' ); insert into `2003_a` values ( 'class1' , '1004' , '73' ); insert into `2003_a` values ( 'class1' , '1005' , '95' ); insert into `2003_a` values ( 'class1' , '1006' , '61' ); insert into `2003_a` values ( 'class1' , '1007' , '77' ); insert into `2003_a` values ( 'class1' , '1008' , '71' ); insert into `2003_a` values ( 'class1' , '1009' , '61' ); insert into `2003_a` values ( 'class1' , '1010' , '78' ); insert into `2003_a` values ( 'class2' , '2001' , '81' ); insert into `2003_a` values ( 'class2' , '2002' , '54' ); insert into `2003_a` values ( 'class2' , '2003' , '57' ); insert into `2003_a` values ( 'class2' , '2004' , '75' ); insert into `2003_a` values ( 'class2' , '2005' , '98' ); insert into `2003_a` values ( 'class2' , '2006' , '75' ); insert into `2003_a` values ( 'class2' , '2007' , '76' ); insert into `2003_a` values ( 'class2' , '2008' , '58' ); insert into `2003_a` values ( 'class2' , '2009' , '73' ); insert into `2003_a` values ( 'class2' , '2010' , '55' ); insert into `2003_a` values ( 'class3' , '3001' , '42' ); insert into `2003_a` values ( 'class3' , '3002' , '90' ); insert into `2003_a` values ( 'class3' , '3003' , '81' ); insert into `2003_a` values ( 'class3' , '3004' , '97' ); insert into `2003_a` values ( 'class3' , '3005' , '68' ); insert into `2003_a` values ( 'class3' , '3006' , '72' ); insert into `2003_a` values ( 'class3' , '3007' , '81' ); insert into `2003_a` values ( 'class3' , '3008' , '79' ); insert into `2003_a` values ( 'class3' , '3009' , '87' ); insert into `2003_a` values ( 'class3' , '3010' , '59' );   -- ---------------------------- -- table structure for `2004_a` -- ---------------------------- drop table if exists `2004_a`; create table `2004_a` (    `tyear` varchar (255) default null ,    `tmonth` varchar (255) default null ,    `sale_money` varchar (255) default null ) engine=innodb default charset=utf8;   -- ---------------------------- -- records of 2004_a -- ---------------------------- insert into `2004_a` values ( '2019' , '10' , '1279' ); insert into `2004_a` values ( '2019' , '11' , '2316' ); insert into `2004_a` values ( '2019' , '12' , '2090' ); insert into `2004_a` values ( '2020' , '01' , '1086' ); insert into `2004_a` values ( '2020' , '02' , '2046' ); insert into `2004_a` values ( '2020' , '03' , '0' ); insert into `2004_a` values ( '2020' , '04' , '2959' ); insert into `2004_a` values ( '2020' , '05' , '1314' ); insert into `2004_a` values ( '2020' , '06' , '2751' ); insert into `2004_a` values ( '2020' , '07' , '1492' ); insert into `2004_a` values ( '2020' , '08' , '1414' ); insert into `2004_a` values ( '2020' , '09' , '2895' ); insert into `2004_a` values ( '2020' , '10' , '2999' ); insert into `2004_a` values ( '2020' , '11' , '1982' ); insert into `2004_a` values ( '2020' , '12' , '2793' ); insert into `2004_a` values ( '2021' , '01' , '2156' ); insert into `2004_a` values ( '2021' , '02' , '1733' ); insert into `2004_a` values ( '2021' , '03' , '2184' );   -- ---------------------------- -- table structure for `t_user` -- ---------------------------- drop table if exists `t_user`; create table `t_user` (    `user_id` int (11) not null auto_increment comment '编号' ,    `user_access` varchar (20) not null default '' comment '账号' ,    `user_token` varchar (20) not null default '123456' comment '密码' ,    `user_nick` varchar (20) not null default '虾米' comment '昵称' ,    `user_gender` bit (1) not null default b '1' comment '1为男，0为女' ,    `user_hobbies` varchar (20) not null comment '爱好' ,    `user_type` int (1) not null default '1' comment '类型' ,    primary key (`user_id`),    unique key `uk_user_access` (`user_access`) using btree ) engine=innodb auto_increment=7 default charset=utf8;   -- ---------------------------- -- records of t_user -- ---------------------------- insert into `t_user` values ( '1' , 'cqswxy' , '111111' , '重庆商务' , '' , '编程,游戏' , '3' ); insert into `t_user` values ( '2' , 'zjczjc' , '222222' , '俊采星驰' , '' , '编程,学习' , '2' ); insert into `t_user` values ( '3' , 'cetoox' , '333333' , '光速为零' , '' , '游戏,学习' , '1' ); insert into `t_user` values ( '4' , 'xxx' , '23' , 'xxx' , '' , 'xxxx' , '1' ); insert into `t_user` values ( '6' , 'dasda' , '123456' , '虾米' , '' , 'asd' , '5' );   -- ---------------------------- -- table structure for `t_user_type` -- ---------------------------- drop table if exists `t_user_type`; create table `t_user_type` (    `user_type_id` int (11) not null auto_increment,    `user_type_name` varchar (2) not null ,    primary key (`user_type_id`) ) engine=innodb auto_increment=5 default charset=utf8;   -- ---------------------------- -- records of t_user_type -- ---------------------------- insert into `t_user_type` values ( '1' , '菜鸟' ); insert into `t_user_type` values ( '2' , '高手' ); insert into `t_user_type` values ( '3' , '传说' ); insert into `t_user_type` values ( '4' , '普通' );

以上就是mysql实现显示百分比显示和前百分之几的方法的详细内容，更多关于mysql 百分比显示的资料请关注服务器之家其它相关文章！

原文链接：https://ymjin.blog.csdn.net/article/details/122093078