MySQL实现显示百分比显示和前百分之几的方法
程序员文章站
2022-06-18 18:45:36
目录要求实现代码数据库前几天一个朋友让我帮忙写的，随手记录一下，感觉难度也不大，就是写的时候遇到一些问题。优化方便做得不太好。有好的优化方法欢迎分享！（数据库在文章结尾）要求1）查询所有时间内，所有产...
前几天一个朋友让我帮忙写的，随手记录一下，感觉难度也不大，就是写的时候遇到一些问题。优化方便做得不太好。有好的优化方法欢迎分享！（数据库在文章结尾）
要求
1）查询所有时间内，所有产品销售金额占比，按占比大小降序排序，筛选累计占比在前80%的产品，结果输出排名产品名称销售金额占比累计占比。
2）查询所有时间内，各个国家的销售情况，销售合计金额大于10000视为业绩合格,
否则为不合格，结果输出国家销售金额业绩情况。
3）查询中国、英国每个月份的销售情况，2020年8月份销售合计金额大于10000视为业绩合格，否则为不合格，2020年9月份销售合计金额大于12000视为业绩合格，否则为不合格，结果输出月份中国销售业绩、英国销售业绩。
实现代码
1）
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）
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）
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 投诉率
示例：
select id,concat(truncate(passscore / (danscore+panscore+duoscore) *100,2),'%') as 成绩与总分比 from aqsc_kaoshi_record;
实现mysql查询前百分之几的数据（这里是80％）
mysql不支持top和rowid，使用limit的方式也行不通。所以使用下面这种方式：
select a.* from (select @rownum:=0) r,2002a a where (@rownum:=@rownum+1)<=(select round(count(*)*0.8) from 2002a);
这里的rownum只是个变量名，也可以是用其他的
将student表的grade从大到小排序后的前20%案例：
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外实现判断显示的示例：
select
sum(case when sex = '男' then 1 else 0 end) /* 这是求男生人数 */
sum(case when sex = '女' then 1 else 0 end) /* 这是求女生人数 */
from student
数据库
以下是数据库完整代码：
/*
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 百分比显示的资料请关注其它相关文章！