A few days ago a friend let me help write, conveniently record, feel difficulty is not big, is to write when encounter some problems. The optimization convenience is not very good. Have a good optimization method welcome to share! (Database at end of article)

requirements

1) Query the proportion of consumption sum of all products in all time, sort them in descending order according to the proportion, screen the products with the top 80% of the total proportion, and output the ranking of the total proportion of consumption sum of product names.

2) Query the sales situation of each country within all time. If the total amount of sales is more than 10000, it will be regarded as qualified; otherwise, it will be regarded as unqualified.

3) Query the sales situation of each month in China and the UK. If the total amount of sales in August 2020 is more than 10000, it will be regarded as qualified; otherwise, it will be regarded as unqualified; if the total amount of sales in September 2020 is more than 12000, it will be regarded as qualified; otherwise, it will be regarded as unqualified.

The implementation code

SELECTAmy polumbo roductID product ID, (a.s ale_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;
Copy the code

SELECTThe country,SUM(price*Sale_amount) sales amount,if(SUM(price*sale_amount)>10000.'qualified'.'Unqualified') PerformanceFROM 2002a a,2002b b,2002c c WHERE a.productID=b.productID AND a.customID=c.customID GROUP BY country;
Copy the code

SELECT date_format(zTime,'%Y-%m') a month,SUM(price*Sale_amount) sales amount, if((date_format(zTime,'%Y-%m')='the 2020-08' AND SUM(price*sale_amount)>10000) OR (date_format(zTime,'%Y-%m')='the 2020-09' AND SUM(price*sale_amount)>13000) AND country='China'.'qualified'.'Unqualified'If ((date_format(zTime,'%Y-%m')='the 2020-08' OR SUM(price*sale_amount)>10000) AND (date_format(zTime,'%Y-%m')='the 2020-09' AND SUM(price*sale_amount)>13000) AND country='the UK'.'qualified'.'Unqualified') UK salesFROM 2002a a,2002b b,2002c c 
WHERE a.productID=b.productID AND a.customID=c.customID AND country IN('China'.'the UK') AND (date_format(zTime,'%Y-%m')='the 2020-09' OR date_format(zTime,'%Y-%m')='the 2020-08') GROUP BY date_format(zTime,'%Y-%m');
Copy the code

(1) to achieve the first eighty percent of query results display method:

Realize the percentage display:

Concat () and left(), TRUNCATE(A,B)

CONCAT(str1,str2,…) Concatenated string that returns the string from the parameter concatenation. If any arguments are NULL, NULL is returned. You can splice more than one.

LEFT(STR,length) intercepts the string from LEFT. Description: left(truncated field, truncated length)

TRUNCATE(A,B) returns the truncated number A to the decimal place B. If B has a value of 0, the result has no decimal point or fractional part. We can set B to be negative if we want to truncate (zero) all of the lowest values from the B decimal place to the left of A. All numbers are rounded near zero

Concat (left (number 1 / number 2 *100,5),’%’) as complaint rate

Example:

SELECT id,CONCAT(TRUNCATE(passScore / (danScore+panScore+duoScore) *100.2),The '%') asThe ratio of grade to total scoreFROM aqsc_kaoshi_record;
Copy the code

Implement the top percent of the mysql query (80% here)

Mysql doesn’t support top and Rowid, and limit doesn’t work either. So use the following:

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

Rownum is just a variable name, but it could be anything else

Select * from student where grade is highest and grade is lowest;

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)
Copy the code

Examples of implementing a judgment display in addition to if:

select 
       sum(case when sex = 'male' then 1 else 0 end)   /* This is the number of boys */
       sum(case when sex = 'woman' then 1 else 0 end)   /* This is the number of girls */
from student
Copy the code

The database

Here 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'.Products' A '.'and');
INSERT INTO `2002b` VALUES ('P002'.Products' B '.'50');
INSERT INTO `2002b` VALUES ('P003'.'product C'.The '42');
INSERT INTO `2002b` VALUES ('P004'.'D products'.'59');
INSERT INTO `2002b` VALUES ('P005'.E 'products'.'49');
INSERT INTO `2002b` VALUES ('P006'.'products F.'10');
INSERT INTO `2002b` VALUES ('P007'.'product G'.'23');
INSERT INTO `2002b` VALUES ('P008'.Products' 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'.Customers' A '.'China');
INSERT INTO `2002c` VALUES ('C002'.Customers' B '.'France');
INSERT INTO `2002c` VALUES ('C003'.Customers' C '.'China');
INSERT INTO `2002c` VALUES ('C004'.Customers' D '.'the UK');
INSERT INTO `2002c` VALUES ('C005'.Customers' E '.'the United States');
INSERT INTO `2002c` VALUES ('C006'.'customer F.'China');
INSERT INTO `2002c` VALUES ('C007'.Customers' G '.'France');
INSERT INTO `2002c` VALUES ('C008'.Customers' H '.'the UK');
INSERT INTO `2002c` VALUES ('C009'.'customer I'.'the United States');
INSERT INTO `2002c` VALUES ('C010'.Customers' H '.'the UK');

-- ----------------------------
-- 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'.The '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 'there' COMMENT 'nickname',
  `user_gender` bit(1) NOT NULL DEFAULT b'1' COMMENT '1 for male, 0 for female ',
  `user_hobbies` varchar(20) NOT NULL COMMENT 'hobby',
  `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'.'Handsome Pick and Star'.' '.'Programming, learning'.'2');
INSERT INTO `t_user` VALUES ('3'.'cetoox'.'333333'.'The speed of light is zero'.' '.'Play, learn'.'1');
INSERT INTO `t_user` VALUES ('4'.'XXX'.'23'.'XXX'.' '.'XXXX'.'1');
INSERT INTO `t_user` VALUES ('6'.'dasda'.'123456'.'there'.' '.'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'.'new');
INSERT INTO `t_user_type` VALUES ('2'.'master');
INSERT INTO `t_user_type` VALUES ('3'.'legends');
INSERT INTO `t_user_type` VALUES ('4'.'ordinary');

Copy the code

Reference:

Mysql query top % of data (25% for example) – CSDN

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

MySQL % displays and displays the top % methods

requirements

The implementation code

The database

MySQL % displays and displays the top % methods

requirements

The implementation code

The database

Related Posts

Big Ali takes you through Sentinel

The stack and queue

Java Multithreading – Thread pools and custom thread pools