• 售前

  • 售后

热门帖子
入门百科

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

[复制链接]
猥琐大叔求教pv 显示全部楼层 发表于 2022-1-9 06:34:31 |阅读模式 打印 上一主题 下一主题
目录


  • 要求
  • 实当代码
  • 数据库
前几天一个朋友让我帮助写的,随手记载一下,感觉难度也不大,就是写的时候碰到一些题目。优化方便做得不太好。有好的优化方法欢迎分享!(数据库在文章末端)

要求

1)查询所偶然间内,全部产品销售金额占比,按占比大小降序排序,筛选累计占比在前80%的产品,结果输出排名产品名称销售金额占比累计占比。
2)查询所偶然间内,各个国家的销售环境,销售合计金额大于10000视为业绩合格,
否则为不合格,结果输出国家销售金额业绩环境。
3)查询中国、英国每个月份的销售环境,2020年8月份销售合计金额大于10000视为业绩合格,否则为不合格,2020年9月份销售合计金额大于12000视为业绩合格,否则为不合格,结果输出月份中国销售业绩、英国销售业绩。

实当代码

1)
  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
  2. FROM (select @rownum:=0) r,2002a a,2002b b
  3. WHERE (@rownum:=@rownum+1)<=(select round(count(distinct a.productid)*0.8) from 2002a a, 2002b b where a.productID = b.productID)
  4. AND a.productID = b.productID GROUP BY a.productID ORDER BY (a.sale_amount * b.price) DESC;
复制代码

2)
  1. SELECT country 国家,SUM(price*sale_amount) 销售金额,if(SUM(price*sale_amount)>10000,'合格','不合格') 业绩情况
  2. FROM 2002a a,2002b b,2002c c WHERE a.productID=b.productID AND a.customID=c.customID GROUP BY country;
复制代码

3)
  1. SELECT date_format(zTime,'%Y-%m') 月份,SUM(price*sale_amount) 销售金额,
  2. 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='中国','合格','不合格') 中国销售业绩,
  3. 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='英国','合格','不合格') 英国销售业绩
  4. FROM 2002a a,2002b b,2002c c
  5. 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. SELECT id,CONCAT(TRUNCATE(passScore / (danScore+panScore+duoScore) *100,2),'%') as 成绩与总分比
  2. FROM aqsc_kaoshi_record;
复制代码
实现mysql查询前百分之几的数据(这里是80%)
mysql不支持top和rowid,使用limit的方式也行不通。以是使用下面这种方式:
  1. SELECT a.*
  2. FROM (SELECT @rownum:=0) r,2002a a
  3. WHERE (@rownum:=@rownum+1)<=(select round(count(*)*0.8) from 2002a);<font face="Arial, Verdana, sans-serif"><span style="white-space: normal;"> </span></font>
复制代码
这里的rownum只是个变量名,也可以是用其他的
将student表的grade从大到小排序后的前20%案例:
  1. SELECT @rownum:=@rownum+1,student.*
  2. FROM (select @rownum:=0) row ,(select * from student order by student.grade desc) student ##排序
  3. WHERE @rownum<(select round(count(*)/4) from student)<font face="Arial, Verdana, sans-serif"><span style="white-space: normal;"> </span></font>
复制代码
除了if外实现判定显示的示例:
  1. select
  2.        sum(case when sex = '男' then 1 else 0 end)   /* 这是求男生人数 */
  3.        sum(case when sex = '女' then 1 else 0 end)   /* 这是求女生人数 */
  4. from student
复制代码
数据库

以下是数据库完整代码:
  1. /*
  2. Navicat MySQL Data Transfer
  3. Source Server         : First
  4. Source Server Version : 80011
  5. Source Host           : localhost:3306
  6. Source Database       : fr_test_sql
  7. Target Server Type    : MYSQL
  8. Target Server Version : 80011
  9. File Encoding         : 65001
  10. Date: 2021-12-18 16:06:19
  11. */
  12. SET FOREIGN_KEY_CHECKS=0;
  13. -- ----------------------------
  14. -- Table structure for `2002a`
  15. -- ----------------------------
  16. DROP TABLE IF EXISTS `2002a`;
  17. CREATE TABLE `2002a` (
  18.   `orderID` varchar(255) NOT NULL,
  19.   `zTime` date NOT NULL,
  20.   `productID` varchar(255) NOT NULL,
  21.   `sale_amount` int(11) NOT NULL,
  22.   `customID` varchar(255) NOT NULL,
  23.   PRIMARY KEY (`orderID`)
  24. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  25. -- ----------------------------
  26. -- Records of 2002a
  27. -- ----------------------------
  28. INSERT INTO `2002a` VALUES ('O001', '2020-09-10', 'P010', '96', 'C008');
  29. INSERT INTO `2002a` VALUES ('O002', '2020-08-29', 'P008', '38', 'C007');
  30. INSERT INTO `2002a` VALUES ('O003', '2020-08-10', 'P007', '97', 'C008');
  31. INSERT INTO `2002a` VALUES ('O004', '2020-09-27', 'P005', '62', 'C006');
  32. INSERT INTO `2002a` VALUES ('O005', '2020-08-17', 'P007', '37', 'C009');
  33. INSERT INTO `2002a` VALUES ('O006', '2020-09-06', 'P006', '3', 'C005');
  34. INSERT INTO `2002a` VALUES ('O007', '2020-08-30', 'P009', '86', 'C007');
  35. INSERT INTO `2002a` VALUES ('O008', '2020-09-04', 'P001', '34', 'C007');
  36. INSERT INTO `2002a` VALUES ('O009', '2020-09-09', 'P003', '99', 'C004');
  37. INSERT INTO `2002a` VALUES ('O010', '2020-09-06', 'P002', '65', 'C010');
  38. INSERT INTO `2002a` VALUES ('O011', '2020-08-08', 'P005', '11', 'C002');
  39. INSERT INTO `2002a` VALUES ('O012', '2020-09-20', 'P002', '3', 'C008');
  40. INSERT INTO `2002a` VALUES ('O013', '2020-08-15', 'P004', '9', 'C004');
  41. INSERT INTO `2002a` VALUES ('O014', '2020-08-28', 'P007', '99', 'C010');
  42. INSERT INTO `2002a` VALUES ('O015', '2020-08-23', 'P003', '3', 'C005');
  43. INSERT INTO `2002a` VALUES ('O016', '2020-08-08', 'P006', '51', 'C008');
  44. INSERT INTO `2002a` VALUES ('O017', '2020-09-04', 'P009', '99', 'C002');
  45. INSERT INTO `2002a` VALUES ('O018', '2020-08-12', 'P007', '86', 'C003');
  46. INSERT INTO `2002a` VALUES ('O019', '2020-09-22', 'P001', '73', 'C005');
  47. INSERT INTO `2002a` VALUES ('O020', '2020-08-03', 'P009', '22', 'C006');
  48. INSERT INTO `2002a` VALUES ('O021', '2020-08-22', 'P007', '54', 'C006');
  49. INSERT INTO `2002a` VALUES ('O022', '2020-09-29', 'P005', '59', 'C005');
  50. INSERT INTO `2002a` VALUES ('O023', '2020-08-15', 'P003', '45', 'C006');
  51. INSERT INTO `2002a` VALUES ('O024', '2020-09-12', 'P001', '10', 'C004');
  52. INSERT INTO `2002a` VALUES ('O025', '2020-08-23', 'P004', '56', 'C008');
  53. INSERT INTO `2002a` VALUES ('O026', '2020-09-17', 'P003', '57', 'C004');
  54. INSERT INTO `2002a` VALUES ('O027', '2020-08-23', 'P002', '73', 'C003');
  55. INSERT INTO `2002a` VALUES ('O028', '2020-09-22', 'P003', '50', 'C008');
  56. INSERT INTO `2002a` VALUES ('O029', '2020-09-22', 'P003', '70', 'C007');
  57. INSERT INTO `2002a` VALUES ('O030', '2020-08-13', 'P006', '15', 'C002');
  58. -- ----------------------------
  59. -- Table structure for `2002b`
  60. -- ----------------------------
  61. DROP TABLE IF EXISTS `2002b`;
  62. CREATE TABLE `2002b` (
  63.   `productID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  64.   `productName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  65.   `price` decimal(10,0) NOT NULL,
  66.   PRIMARY KEY (`productID`)
  67. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  68. -- ----------------------------
  69. -- Records of 2002b
  70. -- ----------------------------
  71. INSERT INTO `2002b` VALUES ('P001', '产品A', '29');
  72. INSERT INTO `2002b` VALUES ('P002', '产品B', '50');
  73. INSERT INTO `2002b` VALUES ('P003', '产品C', '42');
  74. INSERT INTO `2002b` VALUES ('P004', '产品D', '59');
  75. INSERT INTO `2002b` VALUES ('P005', '产品E', '49');
  76. INSERT INTO `2002b` VALUES ('P006', '产品F', '10');
  77. INSERT INTO `2002b` VALUES ('P007', '产品G', '23');
  78. INSERT INTO `2002b` VALUES ('P008', '产品H', '24');
  79. INSERT INTO `2002b` VALUES ('P009', '产品I', '50');
  80. INSERT INTO `2002b` VALUES ('P010', '产品J', '64');
  81. -- ----------------------------
  82. -- Table structure for `2002c`
  83. -- ----------------------------
  84. DROP TABLE IF EXISTS `2002c`;
  85. CREATE TABLE `2002c` (
  86.   `customID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  87.   `customName` varchar(255) NOT NULL,
  88.   `country` varchar(255) NOT NULL,
  89.   PRIMARY KEY (`customID`)
  90. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  91. -- ----------------------------
  92. -- Records of 2002c
  93. -- ----------------------------
  94. INSERT INTO `2002c` VALUES ('C001', '客户A', '中国');
  95. INSERT INTO `2002c` VALUES ('C002', '客户B', '法国');
  96. INSERT INTO `2002c` VALUES ('C003', '客户C', '中国');
  97. INSERT INTO `2002c` VALUES ('C004', '客户D', '英国');
  98. INSERT INTO `2002c` VALUES ('C005', '客户E', '美国');
  99. INSERT INTO `2002c` VALUES ('C006', '客户F', '中国');
  100. INSERT INTO `2002c` VALUES ('C007', '客户G', '法国');
  101. INSERT INTO `2002c` VALUES ('C008', '客户H', '英国');
  102. INSERT INTO `2002c` VALUES ('C009', '客户I', '美国');
  103. INSERT INTO `2002c` VALUES ('C010', '客户H', '英国');
  104. -- ----------------------------
  105. -- Table structure for `2003_a`
  106. -- ----------------------------
  107. DROP TABLE IF EXISTS `2003_a`;
  108. CREATE TABLE `2003_a` (
  109.   `CLASSNO` varchar(255) DEFAULT NULL,
  110.   `STUDENTNO` varchar(255) DEFAULT NULL,
  111.   `GRADE` varchar(255) DEFAULT NULL
  112. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  113. -- ----------------------------
  114. -- Records of 2003_a
  115. -- ----------------------------
  116. INSERT INTO `2003_a` VALUES ('CLASS1', '1001', '86');
  117. INSERT INTO `2003_a` VALUES ('CLASS1', '1002', '60');
  118. INSERT INTO `2003_a` VALUES ('CLASS1', '1003', '85');
  119. INSERT INTO `2003_a` VALUES ('CLASS1', '1004', '73');
  120. INSERT INTO `2003_a` VALUES ('CLASS1', '1005', '95');
  121. INSERT INTO `2003_a` VALUES ('CLASS1', '1006', '61');
  122. INSERT INTO `2003_a` VALUES ('CLASS1', '1007', '77');
  123. INSERT INTO `2003_a` VALUES ('CLASS1', '1008', '71');
  124. INSERT INTO `2003_a` VALUES ('CLASS1', '1009', '61');
  125. INSERT INTO `2003_a` VALUES ('CLASS1', '1010', '78');
  126. INSERT INTO `2003_a` VALUES ('CLASS2', '2001', '81');
  127. INSERT INTO `2003_a` VALUES ('CLASS2', '2002', '54');
  128. INSERT INTO `2003_a` VALUES ('CLASS2', '2003', '57');
  129. INSERT INTO `2003_a` VALUES ('CLASS2', '2004', '75');
  130. INSERT INTO `2003_a` VALUES ('CLASS2', '2005', '98');
  131. INSERT INTO `2003_a` VALUES ('CLASS2', '2006', '75');
  132. INSERT INTO `2003_a` VALUES ('CLASS2', '2007', '76');
  133. INSERT INTO `2003_a` VALUES ('CLASS2', '2008', '58');
  134. INSERT INTO `2003_a` VALUES ('CLASS2', '2009', '73');
  135. INSERT INTO `2003_a` VALUES ('CLASS2', '2010', '55');
  136. INSERT INTO `2003_a` VALUES ('CLASS3', '3001', '42');
  137. INSERT INTO `2003_a` VALUES ('CLASS3', '3002', '90');
  138. INSERT INTO `2003_a` VALUES ('CLASS3', '3003', '81');
  139. INSERT INTO `2003_a` VALUES ('CLASS3', '3004', '97');
  140. INSERT INTO `2003_a` VALUES ('CLASS3', '3005', '68');
  141. INSERT INTO `2003_a` VALUES ('CLASS3', '3006', '72');
  142. INSERT INTO `2003_a` VALUES ('CLASS3', '3007', '81');
  143. INSERT INTO `2003_a` VALUES ('CLASS3', '3008', '79');
  144. INSERT INTO `2003_a` VALUES ('CLASS3', '3009', '87');
  145. INSERT INTO `2003_a` VALUES ('CLASS3', '3010', '59');
  146. -- ----------------------------
  147. -- Table structure for `2004_a`
  148. -- ----------------------------
  149. DROP TABLE IF EXISTS `2004_a`;
  150. CREATE TABLE `2004_a` (
  151.   `TYEAR` varchar(255) DEFAULT NULL,
  152.   `TMONTH` varchar(255) DEFAULT NULL,
  153.   `SALE_MONEY` varchar(255) DEFAULT NULL
  154. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  155. -- ----------------------------
  156. -- Records of 2004_a
  157. -- ----------------------------
  158. INSERT INTO `2004_a` VALUES ('2019', '10', '1279');
  159. INSERT INTO `2004_a` VALUES ('2019', '11', '2316');
  160. INSERT INTO `2004_a` VALUES ('2019', '12', '2090');
  161. INSERT INTO `2004_a` VALUES ('2020', '01', '1086');
  162. INSERT INTO `2004_a` VALUES ('2020', '02', '2046');
  163. INSERT INTO `2004_a` VALUES ('2020', '03', '0');
  164. INSERT INTO `2004_a` VALUES ('2020', '04', '2959');
  165. INSERT INTO `2004_a` VALUES ('2020', '05', '1314');
  166. INSERT INTO `2004_a` VALUES ('2020', '06', '2751');
  167. INSERT INTO `2004_a` VALUES ('2020', '07', '1492');
  168. INSERT INTO `2004_a` VALUES ('2020', '08', '1414');
  169. INSERT INTO `2004_a` VALUES ('2020', '09', '2895');
  170. INSERT INTO `2004_a` VALUES ('2020', '10', '2999');
  171. INSERT INTO `2004_a` VALUES ('2020', '11', '1982');
  172. INSERT INTO `2004_a` VALUES ('2020', '12', '2793');
  173. INSERT INTO `2004_a` VALUES ('2021', '01', '2156');
  174. INSERT INTO `2004_a` VALUES ('2021', '02', '1733');
  175. INSERT INTO `2004_a` VALUES ('2021', '03', '2184');
  176. -- ----------------------------
  177. -- Table structure for `t_user`
  178. -- ----------------------------
  179. DROP TABLE IF EXISTS `t_user`;
  180. CREATE TABLE `t_user` (
  181.   `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  182.   `user_access` varchar(20) NOT NULL DEFAULT '' COMMENT '账号',
  183.   `user_token` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  184.   `user_nick` varchar(20) NOT NULL DEFAULT '虾米' COMMENT '昵称',
  185.   `user_gender` bit(1) NOT NULL DEFAULT b'1' COMMENT '1为男,0为女',
  186.   `user_hobbies` varchar(20) NOT NULL COMMENT '爱好',
  187.   `user_type` int(1) NOT NULL DEFAULT '1' COMMENT '类型',
  188.   PRIMARY KEY (`user_id`),
  189.   UNIQUE KEY `uk_user_access` (`user_access`) USING BTREE
  190. ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
  191. -- ----------------------------
  192. -- Records of t_user
  193. -- ----------------------------
  194. INSERT INTO `t_user` VALUES ('1', 'cqswxy', '111111', '重庆商务', '', '编程,游戏', '3');
  195. INSERT INTO `t_user` VALUES ('2', 'zjczjc', '222222', '俊采星驰', '', '编程,学习', '2');
  196. INSERT INTO `t_user` VALUES ('3', 'cetoox', '333333', '光速为零', '', '游戏,学习', '1');
  197. INSERT INTO `t_user` VALUES ('4', 'XXX', '23', 'XXX', '', 'XXXX', '1');
  198. INSERT INTO `t_user` VALUES ('6', 'dasda', '123456', '虾米', '', 'asd', '5');
  199. -- ----------------------------
  200. -- Table structure for `t_user_type`
  201. -- ----------------------------
  202. DROP TABLE IF EXISTS `t_user_type`;
  203. CREATE TABLE `t_user_type` (
  204.   `user_type_id` int(11) NOT NULL AUTO_INCREMENT,
  205.   `user_type_name` varchar(2) NOT NULL,
  206.   PRIMARY KEY (`user_type_id`)
  207. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  208. -- ----------------------------
  209. -- Records of t_user_type
  210. -- ----------------------------
  211. INSERT INTO `t_user_type` VALUES ('1', '菜鸟');
  212. INSERT INTO `t_user_type` VALUES ('2', '高手');
  213. INSERT INTO `t_user_type` VALUES ('3', '传说');
  214. INSERT INTO `t_user_type` VALUES ('4', '普通');
  215. <font face="Arial, Verdana, sans-serif"><span style="white-space: normal;"> </span></font>
复制代码
以上就是MySQL实现显示百分比显示和前百分之几的方法的详细内容,更多关于MySQL 百分比显示的资料请关注草根技术分享别的相干文章!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作