How to display percentage and the first few percent in MySQL

How to display percentage and the first few percent in MySQL

A friend asked me to help write this a few days ago. I just wrote it down casually. It doesn’t seem to be difficult, but I encountered some problems while writing. The optimization is not very good. If you have any good optimization methods, please share them! (Database at the end of the article)

Require

1) Query the sales amount ratio of all products in all time periods, sort them in descending order by ratio, filter the products with the top 80% cumulative ratio, and output the ranking product name, sales amount ratio, and cumulative ratio.

2) Check the sales situation in each country at all times. The sales amount greater than 10,000 is considered qualified.

Otherwise it is unqualified and the result is output as national sales amount performance.

3) Query the sales situation of China and the UK for each month. If the total sales amount in August 2020 is greater than 10,000, it is considered to be qualified, otherwise it is unqualified. If the total sales amount in September 2020 is greater than 12,000, it is considered to be qualified, otherwise it is unqualified. The results output the sales performance of China and the sales performance of the UK for that month.

Implementation Code

1)

SELECT a.productID product ID,(a.sale_amount * b.price) sales amount,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 country,SUM(price*sale_amount) sales amount,if(SUM(price*sale_amount)>10000,'qualified','unqualified') performance 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') month, SUM(price*sale_amount) sales 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='中国','合格','未合格') China sales performance,
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='UK','Qualified','Unqualified') UK sales performance 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');

Method to display the first 80% of query results:

Achievement percentage display:

First, let's learn about the two functions concat() and left(), TRUNCATE(A,B)

CONCAT(str1,str2,...) concatenates strings and returns the string resulting from the concatenation of the parameters. If any argument is NULL, returns NULL. Can be spliced ​​multiple times.

LEFT(str,length) intercepts the string from the left. Description: left(intercepted field, interception length)

TRUNCATE(A,B) returns the number A truncated to B decimal places. If the value of B is 0, the result has no decimal point or no decimal part. B can be set to a negative number to truncate (return to zero) all the low-order values ​​starting from the Bth digit to the left of the decimal point of A. All numbers are rounded towards zero.

Combine it (I didn’t use left in the code above): concat ( left (value1 / value2 *100,5),'%') as complaint rate

Example:

SELECT id,CONCAT(TRUNCATE(passScore / (danScore+panScore+duoScore) *100,2),'%') as score to total score ratio FROM aqsc_kaoshi_record;

Implement MySQL query to get the first few percent of data (here is 80%)

MySQL does not support top and rowid, and using limit does not work. So use the following approach:

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

The rownum here is just a variable name, you can also use other

The first 20% cases after sorting the grade of the student table from largest to smallest:

SELECT @rownum:=@rownum+1,student.* 
FROM (select @rownum:=0) row ,(select * from student order by student.grade desc) student ##sort WHERE @rownum<(select round(count(*)/4) from student)

In addition to if, an example of implementing judgment display:

select 
       sum(case when sex = '男' then 1 else 0 end) /* This is to find the number of male students*/
       sum(case when sex = '女' then 1 else 0 end) /* This is to find the number of girls*/
from student

database

The following is the complete database code:

/*
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', 'Product A', '29');
INSERT INTO `2002b` VALUES ('P002', 'Product B', '50');
INSERT INTO `2002b` VALUES ('P003', 'Product C', '42');
INSERT INTO `2002b` VALUES ('P004', 'Product D', '59');
INSERT INTO `2002b` VALUES ('P005', 'Product E', '49');
INSERT INTO `2002b` VALUES ('P006', 'Product F', '10');
INSERT INTO `2002b` VALUES ('P007', 'Product G', '23');
INSERT INTO `2002b` VALUES ('P008', 'Product H', '24');
INSERT INTO `2002b` VALUES ('P009', 'Product I', '50');
INSERT INTO `2002b` VALUES ('P010', 'Product 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', 'Customer A', 'China');
INSERT INTO `2002c` VALUES ('C002', 'Customer B', 'France');
INSERT INTO `2002c` VALUES ('C003', 'Customer C', 'China');
INSERT INTO `2002c` VALUES ('C004', 'Customer D', 'United Kingdom');
INSERT INTO `2002c` VALUES ('C005', 'Customer E', 'United States');
INSERT INTO `2002c` VALUES ('C006', 'Customer F', 'China');
INSERT INTO `2002c` VALUES ('C007', 'Customer G', 'France');
INSERT INTO `2002c` VALUES ('C008', 'Customer H', 'United Kingdom');
INSERT INTO `2002c` VALUES ('C009', 'Customer I', 'United States');
INSERT INTO `2002c` VALUES ('C010', 'Customer H', 'United Kingdom');

-- ----------------------------
-- 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 'Number',
  `user_access` varchar(20) NOT NULL DEFAULT '' COMMENT 'Account',
  `user_token` varchar(20) NOT NULL DEFAULT '123456' COMMENT 'Password',
  `user_nick` varchar(20) NOT NULL DEFAULT '虾米' COMMENT '妮名',
  `user_gender` bit(1) NOT NULL DEFAULT b'1' COMMENT '1 for male, 0 for female',
  `user_hobbies` varchar(20) NOT NULL COMMENT 'Hobbies',
  `user_type` int(1) NOT NULL DEFAULT '1' COMMENT 'Type',
  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', 'Chongqing Business', '', 'Programming, Games', '3');
INSERT INTO `t_user` VALUES ('2', 'zjczjc', '222222', '俊采星驰', '', 'Programming, learning', '2');
INSERT INTO `t_user` VALUES ('3', 'cetoox', '333333', 'The speed of light is zero', '', 'Game, study', '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', 'rookie');
INSERT INTO `t_user_type` VALUES ('2', 'Expert');
INSERT INTO `t_user_type` VALUES ('3', 'Legend');
INSERT INTO `t_user_type` VALUES ('4', 'Normal');

The above is the details of how to display percentages and the first few percents in MySQL. For more information about MySQL percentage display, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MySQL query results are displayed in percentages in a simple way
  • Use of select, distinct, and limit in MySQL
  • The impact of limit on query performance in MySQL
  • Essential conditional query statements for MySQL database

<<:  Design a simple HTML login interface using CSS style

>>:  Block-level and line-level elements, special characters, and nesting rules in HTML

Recommend

Solution to the problem that the number of MySQL connections is limited to 214 in CentOS 7

Find the problem Recently, I encountered a proble...

Tutorial on disabling and enabling triggers in MySQL [Recommended]

When using MYSQL, triggers are often used, but so...

How to execute Linux shell commands in Docker

To execute a shell command in Docker, you need to...

Implementation of the login page of Vue actual combat record

Table of contents 1. Preliminary preparation 1.1 ...

Linux five-step build kernel tree

Table of contents 0. The kernel tree that comes w...

Detailed tutorial for installing winx64 under mysql8.0.18 (with pictures and text)

Go to https://dev.mysql.com/downloads/mysql/ to d...

Summary of Form Design Techniques in Web Design

“Inputs should be divided into logical groups so ...

A brief discussion on order reconstruction: MySQL sharding

Table of contents 1. Objectives 2. Environmental ...

React encapsulates the global bullet box method

This article example shares the specific code of ...

Learn how to deploy and start multiple tomcats and migrate projects in one article

Table of contents Deploy tomcat 1. Download and d...

WeChat applet implements simple chat room

This article shares the specific code of the WeCh...

JavaScript quickly implements calendar effects

This article example shares the specific code of ...

Thoughts on truncation of multi-line text with a "show more" button

I just happened to encounter this small requireme...

Docker deployment and installation steps for Jenkins

First, we need a server with Docker installed. (I...

Solution to the problem of mysql master-slave switch canal

After configuring VIP, the error message that app...