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

Detailed explanation of Tomcat directory structure

Table of contents Directory Structure bin directo...

Detailed explanation of Linux command unzip

Table of contents 1. unzip command 1.1 Syntax 1.2...

In-depth understanding of slot-scope in Vue (suitable for beginners)

There are already many articles about slot-scope ...

Docker container exits after running (how to keep running)

Phenomenon Start the Docker container docker run ...

Installing Win10 system on VMware workstation 14 pro

This article introduces how to install the system...

JavaScript dynamically generates a table with row deletion function

This article example shares the specific code of ...

HTML Several Special Dividing Line Effects

1. Basic lines 2. Special effects (the effects ar...

Problems and solutions of error 08001 when linking to MySQL in IDEA and no table display after successful connection

Error: Connection to blog0@localhost failed. [080...

The problem of two requests when the src attribute value of the img tag is empty (non-IE browser)

When the img src value is empty, two requests are ...

Detailed explanation of React setState data update mechanism

Table of contents Why use setState Usage of setSt...

Implementation of navigation bar and drop-down menu in CSS

1. CSS Navigation Bar (1) Function of the navigat...

Specific use of Mysql prepare preprocessing

Table of contents 1. Preprocessing 2. Pretreatmen...

Tutorial on installing Seurat2 and Seurat3 simultaneously using the same version of R in Linux

Seurat is a heavyweight R package for single-cell...

Detailed steps for using AES.js in Vue

Use of AES encryption Data transmission encryptio...

How to quickly build your own server detailed tutorial (Java environment)

1. Purchase of Server 1. I chose Alibaba Cloud...