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

Blog    

Recommend

Vue-Element-Admin integrates its own interface to realize login jump

1. First look at the request configuration file, ...

CentOS6.8 Chinese/English environment switching tutorial diagram

1. Introduction People who are not used to Englis...

How to configure SSL certificate in nginx to implement https service

In the previous article, after using openssl to g...

Implementation of nginx proxy port 80 to port 443

The nginx.conf configuration file is as follows u...

How to embed flash video format (flv, swf) files in html files

Flash file formats: .FLV and .SWF There are two ex...

Detailed explanation of LVM seamless disk horizontal expansion based on Linux

environment name property CPU x5650 Memory 4G dis...

JavaScript to implement simple tab bar switching content bar

This article shares the specific code of JavaScri...

Detailed explanation of installing redis in docker and starting it as a configuration file

Update: Recently, it was discovered that the serv...

CSS to achieve zoom in and out close button (example code)

This effect is most common on our browser page. L...

Detailed explanation of encoding issues during MySQL command line operations

1. Check the MySQL database encoding mysql -u use...

MySQL slow query method and example

1. Introduction By enabling the slow query log, M...

HTML+CSS to achieve charging water drop fusion special effects code

Table of contents Preface: accomplish: Summarize:...

How to use tcpdump to capture packets in Linux system

Let me look at the example code first: 1. Common ...

Detailed explanation of the role of the new operator in Js

Preface Js is the most commonly used code manipul...

MySQL installation diagram summary

MySQL 5.5 installation and configuration method g...