前几天一个朋友让我帮忙写的，随手记录一下，感觉难度也不大，就是写的时候遇到一些问题。优化方便做得不太好。有好的优化方法欢迎分享！（数据库在文章结尾）
要求
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 transfersource server : firstsource server version : 80011source host : localhost:3306source database : fr_test_sqltarget server type : mysqltarget server version : 80011file encoding : 65001date: 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
