需求1

查询两个表之间你需要的数据:

主表fi_inspect_result_detail: 1557755905903

次表fi_task: 155775215903

需要查多个字段,然后计算差值,再判断在允差范围内的正常和超标标记"danger":

sql语句如下

SELECT
a.id,
a.rollNum,
a.verifiedLength,
a.supplierLength,
(SELECT CEIL(COUNT(id)/10) FROM fi_inspect_result_detail WHERE taskId = 2472) AS totalPage,
100-ROUND((a.verifiedLength/a.supplierlength*100),2)AS lengthPercent,
CASE WHEN 100-ROUND((a.verifiedLength/a.supplierlength*100),2)>3 OR 100-ROUND((a.verifiedLength/a.supplierlength*100),2)<-3
THEN "danger " ELSE "safety" END AS lengthPercentStatus,
a.actualUsableWidth,
a.supplierUsableWidth,
100-ROUND((a.actualUsableWidth/a.supplierUsableWidth*100),2) AS usableWidthPercent,
CASE
WHEN 100-ROUND((a.actualUsableWidth/a.supplierUsableWidth*100),2)>5 OR 100-ROUND((a.actualUsableWidth/a.supplierUsableWidth*100),2)<-5
THEN "danger" ELSE "safety" END AS usableWidthPerecentStatus,
a.actualGsm,
b.gramWeight,
100-ROUND((a.actualGsm/b.gramWeight*100),2) AS gsmPercent,
CASE
WHEN 100-ROUND((a.actualGsm/b.gramWeight*100),2)>2 OR 100-ROUND((a.actualGsm/b.gramWeight*100),2)<-2
THEN "danger" ELSE "safety" END AS gsmPerecentStatus,
a.actualGrosssWeight,
a.supplierGrossWeight,
100-ROUND((a.actualGrosssWeight/a.supplierGrossWeight*100),2) AS grossWeightPercent,
CASE
WHEN 100-ROUND((a.actualGrosssWeight/a.supplierGrossWeight*100),2)>2 OR 100-ROUND((a.actualGrosssWeight/a.supplierGrossWeight*100),2)<-2
THEN "danger" ELSE "safety" END AS grossWeightPerecentStatus,
a.actualNetWeight,
a.supplierNetWeight,
100-ROUND((a.actualNetWeight/a.supplierNetWeight*100),2) AS netWeightPercent,
CASE
WHEN 100-ROUND((a.actualNetWeight/a.supplierNetWeight*100),2)>2 OR 100-ROUND((a.actualNetWeight/a.supplierNetWeight*100),2)<-2
THEN "danger" ELSE "safety" END AS netWeightPerecentStatus
FROM fi_inspect_result_detail a
LEFT JOIN fi_task b
ON a.taskId = b.id
WHERE a.taskId = 2472
AND a.TYPE=1
AND a.isValid=1
ORDER BY lengthPercent DESC
LIMIT 80,10;

运行结果为: 1557755905903 图中红色标记意思对应为 "分页总数量"--totalPage,"差值计算出来的百分比"----****percent,"百分比状态是否正常"-----percentStatus

用命令行运行结果 1557755905903 查询完成。。。。。。

+--------+---------+----------------+----------------+-----------+---------------+---------------------+-------------------+---------------------+--------------------+---------------------------+-----------+------------+------------+-------------------+--------------------+---------------------+--------------------+---------------------------+-----------------+-------------------+------------------+-------------------------+
| id     | rollNum | verifiedLength | supplierLength | totalPage | lengthPercent | lengthPercentStatus | actualUsableWidth | supplierUsableWidth | usableWidthPercent | usableWidthPerecentStatus | actualGsm | gramWeight | gsmPercent | gsmPerecentStatus | actualGrosssWeight | supplierGrossWeight | grossWeightPercent | grossWeightPerecentStatus | actualNetWeight | supplierNetWeight | netWeightPercent | netWeightPerecentStatus |
+--------+---------+----------------+----------------+-----------+---------------+---------------------+-------------------+---------------------+--------------------+---------------------------+-----------+------------+------------+-------------------+--------------------+---------------------+--------------------+---------------------------+-----------------+-------------------+------------------+-------------------------+
| 178026 | 1-8     |           99.3 |          101.4 |        14 |          2.07 | safety              |               134 |               136.1 |               1.54 | safety                    |       120 |        186 |      35.48 | danger            |               16.3 |                32.3 |              49.54 | danger                    |              16 |             30.91 |            48.24 | danger                  |
| 178027 | 1-8     |           99.3 |          101.4 |        14 |          2.07 | safety              |               134 |               136.1 |               1.54 | safety                    |       120 |        186 |      35.48 | danger            |               16.3 |                32.3 |              49.54 | danger                    |              16 |             30.91 |            48.24 | danger                  |
| 177971 | 1-8     |           99.3 |          101.4 |        14 |          2.07 | safety              |               134 |               136.1 |               1.54 | safety                    |       120 |        186 |      35.48 | danger            |               16.3 |                32.3 |              49.54 | danger                    |              16 |             30.91 |            48.24 | danger                  |
| 177639 | 1-8     |           99.3 |          101.4 |        14 |          2.07 | safety              |               134 |               136.1 |               1.54 | safety                    |       120 |        186 |      35.48 | danger            |               16.3 |                32.3 |              49.54 | danger                    |              16 |             30.91 |            48.24 | danger                  |
| 178028 | 1-9     |            143 |            146 |        14 |          2.05 | safety              |               135 |               136.1 |               0.81 | safety                    |       120 |        186 |      35.48 | danger            |               24.2 |                32.3 |              25.08 | danger                    |            23.9 |             30.91 |            22.68 | danger                  |
| 178029 | 1-9     |            143 |            146 |        14 |          2.05 | safety              |               135 |               136.1 |               0.81 | safety                    |       120 |        186 |      35.48 | danger            |               24.2 |                32.3 |              25.08 | danger                    |            23.9 |             30.91 |            22.68 | danger                  |
| 177978 | 1-9     |            143 |            146 |        14 |          2.05 | safety              |               135 |               136.1 |               0.81 | safety                    |       120 |        186 |      35.48 | danger            |               24.2 |                32.3 |              25.08 | danger                    |            23.9 |             30.91 |            22.68 | danger                  |
| 177646 | 1-9     |            143 |            146 |        14 |          2.05 | safety              |               135 |               136.1 |               0.81 | safety                    |       120 |        186 |      35.48 | danger            |               24.2 |                32.3 |              25.08 | danger                    |            23.9 |             30.91 |            22.68 | danger                  |
| 177997 | 1-3     |          151.6 |          154.7 |        14 |          2.00 | safety              |               135 |               136.1 |               0.81 | safety                    |       120 |        186 |      35.48 | danger            |              25.45 |                32.3 |              21.21 | danger                    |           25.15 |             30.91 |            18.63 | danger                  |
| 178015 | 1-3     |          151.6 |          154.7 |        14 |          2.00 | safety              |               135 |               136.1 |               0.81 | safety                    |       120 |        186 |      35.48 | danger            |              25.45 |                32.3 |              21.21 | danger                    |           25.15 |             30.91 |            18.63 | danger                  |
+--------+---------+----------------+----------------+-----------+---------------+---------------------+-------------------+---------------------+--------------------+---------------------------+-----------+------------+------------+-------------------+--------------------+---------------------+--------------------+---------------------------+-----------------+-------------------+------------------+-------------------------+
10 rows in set (0.00 sec)

需求2

根据前端请求,页面数和分页数,下发对应的数据给前端显示,未定。。。

2019.08.27 补充:上次更到一半之后忘记补了

因为一次性下发数据和总条数不太优雅(其实是我不会,,,),所以,分页另外写一条sql,查询 筛选完之后的总条数,也就是 前端要显示的分页数量。如1.2.3.4.5...10 例

需求确定,获取筛选完的分页数

上面的sql写的 稍显复杂,这里我们只需要获取总条数,因此一些条件可以省去,结合思路,sql整理如下:
SELECT COUNT(*) AS totalPage FROM 
(SELECT a.id,a.rollNum,
100-ROUND((a.verifiedLength/a.supplierlength*100),2)AS lengthPercent,
100-ROUND((a.actualUsableWidth/a.supplierUsableWidth*100),2) AS usableWidthPercent,
100-ROUND((a.actualGsm/b.gramWeight*100),2) AS gsmPercent,
100-ROUND((a.actualGrosssWeight/a.supplierGrossWeight*100),2) AS grossWeightPercent,
100-ROUND((a.actualNetWeight/a.supplierNetWeight*100),2) AS netWeightPercent
FROM fi_inspect_result_detail a
LEFT JOIN fi_task b
ON a.taskId = b.id
WHERE a.taskId = 2472
AND a.TYPE=1
AND a.isValid=1
HAVING lengthPercent > '-2'
) heart

这个地方有个逻辑,用HAVING 筛选的问题,前端传过来的筛选条件肯定是变化的,因此,这里采用了拼接sql的方式来接HAVING 包含的筛选条件,然后去数据库查询,获取总条数。

1557755905903

查询结果为总条数,查询举例

1557755905903

至此完成一个小Demo

0911更新:

今天get了一个新姿势,substring不仅可以用在java上,在MySQL里面,居然也能用,有时间真的得好好深入研究下MySQL了,功能强大的一批,不知道之前,我还在琢磨着怎么将text转型......

拼接字符串,简单介绍下用法:

substring_index(str,delim,count)

      str:要处理的字符串

      delim:分隔符

      count:计数

例子:str=www.noheart.cn

  substring_index(str,'.',1)

  结果是:www

  substring_index(str,'.',2)

  结果是:www.noheart

  也就是说,如果count是正数,那么就是从左往右数,第N个分隔符的左边的全部内容

  相反,如果是负数,那么就是从右边开始数,第N个分隔符右边的所有内容,如:

  substring_index(str,'.',-2)

  结果为:noheart.cn

 有人会问,如果我要中间的的wikibt怎么办?

 很简单的,两个方向:

  从右数第二个分隔符的右边全部,再从左数的第一个分隔符的左边:

  substring_index(substring_index(str,'.',-2),'.',1);

mysql> SELECT substring_index('www.noheart.cn','.',1) AS col1,substring_index(substring_index('www.noheart.cn','.',-2),'.',1) AS coll2;
+------+---------+
| col1 | coll2   |
+------+---------+
| www  | noheart |
+------+---------+
1 row in set (0.00 sec)

新的需求是要查询卷id,将存储为text结构的 其实就是int类型的数组,将其中的数值取出来 分别一对一 取得卷ID对应的内容 sql语句如下

mysql> SELECT A.LEVEL,A.remark,A.picGroupId,A.dyelotNo,CASE WHEN LEVEL IN ('4','4/5','5','0') THEN 'Conformed' ELSE 'NotConformed' END AS operateUserName,
SUBSTRING_INDEX(substring_index(A.volumeIds,',',B.help_topic_id+1),',',-1) AS volumeIds FROM fba_head_tail_difference AS A JOIN mysql.help_topic AS B 
ON B.help_topic_id< (LENGTH(A.volumeIds)-length(REPLACE (A.volumeIds,',',''))+1) WHERE taskId=2125 AND A.isValid=1 HAVING LEVEL='3/4';
+-------+------------+-------------------------------------------------------------------------------------+----------+-----------------+-----------+
| LEVEL | remark     | picGroupId                                                                          | dyelotNo | operateUserName | volumeIds |
+-------+------------+-------------------------------------------------------------------------------------+----------+-----------------+-----------+
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2        | NotConformed    | 25121     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2        | NotConformed    | 25122     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2        | NotConformed    | 25124     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2        | NotConformed    | 25127     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2        | NotConformed    | 25133     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 2        | NotConformed    | 25135     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 3        | NotConformed    | 25145     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 3        | NotConformed    | 25159     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 4        | NotConformed    | 25164     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 4        | NotConformed    | 25176     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 5        | NotConformed    | 25203     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 6        | NotConformed    | 25204     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 6        | NotConformed    | 25211     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 6        | NotConformed    | 25212     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 6        | NotConformed    | 25217     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 6        | NotConformed    | 25225     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 7        | NotConformed    | 25229     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 7        | NotConformed    | 25230     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 7        | NotConformed    | 25239     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 8        | NotConformed    | 25245     |
| 3/4   | 存在色相差 | 41966-41967,41968-41969,41970-41971,41972-41973,41974-41975,41976-41977,41978-41979 | 8        | NotConformed    | 25256     |
+-------+------------+-------------------------------------------------------------------------------------+----------+-----------------+-----------+
21 rows in set (0.03 sec)

至此,over,完成需求,再一次的感叹MySQL的强大,以及自己多么的菜菜

知之事益多,不知之其事益多也

且学且知己浅

愈学愈觉自知

学愈进而愈惘

愈学愈觉己不足,日日初学有新天

我在使用MySQL8.0.11时候,用的sqlyog图形化工具,导入csv,excel格式的数据时候,提示:

The used command is not allowed with this MySQL version 于是我尝试使用命令行导入数据, LOAD DATA local INFILE

于是在sql编辑器中输入 SHOW VARIABLES LIKE '%local%';

local_infile OFF 即该变量未开启。

解决方法:

输入SET GLOBAL local_infile=1(其中global是全局变量的意思)。

注意:这样还是解决不了~

于是!我们命令行登陆MySQL时附上一句声明:

 mysql --local-infile -uroot -ppassword

重新进行导入数据操作:

LOAD DATA LOCAL INFILE 'G:/data.txt' INTO TABLE chinadb (id,parentId,name,country,province,city,district,levelType,cityCode,zipCode,pinYin,JianPin,firstChar,remarks);

133764 搞定~

MySQL 对于数字,字符,数字 组合的字段 进行排序:

如 1-2,1-3,1-2,2-3,1-9,1-6

我们直接 ORDER BY 对于它进行排序是不严谨的

因此,我们可以对该字段进行 拆分 为两个数字 分别排序:

用到SUBSTRING_INDEX 这个函数,

substring_index(str,delim,count)

str:要处理的字符串

delim:分隔符

count:计数

直接上代码:

SELECT SUBSTRING_INDEX(rollNum,'-',1)AS sortFirst,SUBSTRING_INDEX(rollNum,'-',-1)AS sortSecond,rollNum FROM a WHERE id=2125 ORDER BY sortFirst,CAST(sortSecond AS UNSIGNED)
+-----------+------------+---------+
| sortFirst | sortSecond | rollNum |
+-----------+------------+---------+
| 1         | 1          | 1-1     |
| 1         | 2          | 1-2     |
| 1         | 3          | 1-3     |
| 1         | 4          | 1-4     |
| 1         | 5          | 1-5     |
| 1         | 6          | 1-6     |
| 1         | 7          | 1-7     |
| 1         | 8          | 1-8     |
| 1         | 9          | 1-9     |
| 1         | 10         | 1-10    |
| 1         | 11         | 1-11    |
| 1         | 12         | 1-12    |
| 1         | 13         | 1-13    |
| 1         | 14         | 1-14    |
| 1         | 15         | 1-15    |
| 1         | 16         | 1-16    |
| 1         | 17         | 1-17    |
| 1         | 18         | 1-18    |
| 1         | 19         | 1-19    |
| 1         | 20         | 1-20    |
| 1         | 21         | 1-21    |
| 2         | 22         | 2-22    |
| 2         | 23         | 2-23    |
| 2         | 24         | 2-24    |
| 2         | 25         | 2-25    |
| 2         | 27         | 2-27    |
| 2         | 31         | 2-31    |
| 2         | 34         | 2-34    |
| 2         | 36         | 2-36    |
| 2         | 38         | 2-38    |
| 2         | 40         | 2-40    |
| 2         | 42         | 2-42    |
| 2         | 43         | 2-43    |
| 2         | 45         | 2-45    |
| 2         | 48         | 2-48    |
| 2         | 50         | 2-50    |
| 2         | 52         | 2-52    |
| 2         | 54         | 2-54    |
| 2         | 55         | 2-55    |
| 2         | 58         | 2-58    |
| 2         | 60         | 2-60    |
| 2         | 62         | 2-62    |
| 3         | 26         | 3-26    |
| 3         | 28         | 3-28    |
| 3         | 29         | 3-29    |
| 3         | 30         | 3-30    |
| 3         | 32         | 3-32    |
| 3         | 33         | 3-33    |
| 3         | 35         | 3-35    |
| 3         | 37         | 3-37    |
| 3         | 39         | 3-39    |
| 3         | 41         | 3-41    |
| 3         | 44         | 3-44    |
| 4         | 64         | 4-64    |
| 4         | 67         | 4-67    |
| 4         | 72         | 4-72    |

上面代码" CAST(sortSecond AS UNSIGNED) ",它的属性:就是将数字类型无符号化,通俗的来讲,就是变成非负数,在无负数的情况下,SIGNED比UNSIGNED的性能相应更高

如上所述,实现对字符串的升序排序,

新增:GROUP BY 语法

“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组,所谓的分组就是将一个“数据集”划分成若干个“小区域”,然后针对若干个“小区域”进行数据处理。

SELECT name FROM test
GROUP BY name

GROUP BY 单个字段和多个字段 本质上其实是一样的;

GROUP BY多个字段可以把它看成一个整体字段,以他们整体来进行分组的

GROUP BY可以配合聚合函数来用,分组之后你可以计数(COUNT),求和(SUM),求平均数(AVG)等。

#常用聚合函数

count() 计数

sum() 求和

avg() 平均数

max() 最大值

min() 最小值

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

weblog

如果我们现在想知道每个部门有多少名在职员工,步骤如下:

筛选在职员工 where to_date='9999-01-01'; 对部门进行分组group by dept_no 对员工进行计数 count(emp_no)

SELECT
  dept_no as 部门,
  count( emp_no) as 人数
FROM
  dept_emp 
WHERE
  to_date = '9999-01-01' 
GROUP BY
  dept_no

image.png


医不自医,人不渡己