根据需求实现mysql脚本--笔记

需求1

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

主表fi_inspect_result_detail：

次表fi_task：

需要查多个字段，然后计算差值，再判断在允差范围内的正常和超标,正常则为"safety",超标标记则"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;

运行结果为:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KwDrf3jQ-1600835162737)(https://noheart.cn/upload/mysql/11307.png)]
图中红色标记意思对应为 “分页总数量”–totalPage,“差值计算出来的百分比”----****percent,“百分比状态是否正常”-----percentStatus

用命令行运行结果

查询完成。。。。。。

+--------+---------+----------------+----------------+-----------+---------------+---------------------+-------------------+---------------------+--------------------+---------------------------+-----------+------------+------------+-------------------+--------------------+---------------------+--------------------+---------------------------+-----------------+-------------------+------------------+-------------------------+
| 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