★计算同一记录中不同字段之和

背景说明:MainDB表内有几十个字段用于存放配方数据,需要将它们加起来看看是否归一. 由于MySQL没有类似高版本Access那样的计算字段,因为需要用SQL语法来处理.

自定义2个术语:

横向求和:即同一条记录内不同数值字段之间求和, 用加号来处理即可.

纵向求和:即不同记录内同一数值字段之间求和, MySQL里面有SUM函数可用.

技巧1: 横向求和: 各字段之间用加号求和(不能用Sum,它用于纵向求和,此为横向求和)

技巧2: 由于某些字段值可能为NULL,直接相加结果会成为NULL导致数值被掩蔽,需要将NULL转化为0,使用MySQL的coalesce函数处理.

技巧3:  如果需要找出求和值与某一字段(此表中原有一个总和字段,它事先在导入数据库之前就计算过.但数据库处理过程中可能存在bug导致某些字段没有成功导入却没有被发觉,因此需要检验完整性),可以用Having子句,将前面计算出来的总和值与数据库内已经保存的字段值[总和]进行相减,求绝对值后再判断是否大于0.001.即误差是否超过0.001(单位为%)

技巧4:  MySQL中DEC即是Decimal的简称,也是系统的保留词,溶剂字段DEC要用单反引号包围起来,即`DEC`; 一般的字段名称可以用单反引号包围,也可以不用.

以下为SQL语句:

SELECT  RecID, 配方名称,(

    COALESCE ( LIPF6, 0 ) + COALESCE ( EC, 0 ) + COALESCE ( PC, 0 ) + COALESCE ( DMC, 0 ) + COALESCE ( EMC, 0 ) + COALESCE ( `DEC`, 0 ) + COALESCE ( EP, 0 ) + COALESCE ( PP, 0 ) + COALESCE ( MA, 0 ) + COALESCE ( EA, 0 ) + COALESCE ( PA, 0 ) + COALESCE ( MPC, 0 ) + COALESCE ( GBL, 0 ) + COALESCE ( MP, 0 ) + COALESCE ( MB, 0 ) + COALESCE ( EB, 0 ) + COALESCE ( VC, 0 ) + COALESCE ( FEC, 0 ) + COALESCE ( PS, 0 ) + COALESCE ( DTD, 0 ) + COALESCE ( LIPF2O2, 0 ) + COALESCE ( LIBOB, 0 ) + COALESCE ( LIDFOB, 0 ) + COALESCE ( LIFSI, 0 ) + COALESCE ( WCA2, 0 ) + COALESCE ( LIBF4, 0 ) + COALESCE ( TMSB, 0 ) + COALESCE ( TMSP, 0 ) + COALESCE ( SN, 0 ) + COALESCE ( ADN, 0 ) + COALESCE ( HTCN, 0 ) + COALESCE ( DENE, 0 ) + COALESCE ( DCB, 0 ) + COALESCE ( BP, 0 ) + COALESCE ( CHB, 0 ) + COALESCE ( TOL, 0 ) + COALESCE ( FB, 0 ) + COALESCE ( FT, 0 ) + COALESCE ( TBB, 0 ) + COALESCE ( TAB, 0 ) + COALESCE ( DDB, 0 ) + COALESCE ( TPPI, 0 ) + COALESCE ( HMDS, 0 ) + COALESCE ( VEC, 0 ) + COALESCE ( PST, 0 ) + COALESCE ( MMDS, 0 ) + COALESCE ( D2, 0 ) + COALESCE ( PFPN, 0 ) + COALESCE ( PSA, 0 ) + COALESCE ( BS, 0 ) + COALESCE ( SA, 0 ) + COALESCE ( MAN, 0 ) + COALESCE ( CA, 0 ) + COALESCE ( LITFSI, 0 ) + COALESCE ( HDI, 0 ) + COALESCE ( REV2, 0 ) + COALESCE ( REV3, 0 ) + COALESCE ( REV4, 0 ) + COALESCE ( REV5, 0 ) + COALESCE ( REV6, 0 ) + COALESCE ( REV7, 0 ) + COALESCE ( REV8, 0 ) + COALESCE ( REV9, 0 ) + COALESCE ( REV10, 0 ) + COALESCE ( REV11, 0 ) + COALESCE ( REV12, 0 ) + COALESCE ( REV13, 0 ) + COALESCE ( REV14, 0 ) + COALESCE ( REV15, 0 ) + COALESCE ( REV16, 0 ) + COALESCE ( REV17, 0 ) + COALESCE ( REV18, 0 ) + COALESCE ( AGENT6, 0 ) + COALESCE ( AGENT7, 0 ) + COALESCE ( AGENT8, 0 ) + COALESCE ( AGENT9, 0 ) + COALESCE ( AGENT4, 0 ) + COALESCE ( AGENT5, 0 ) + COALESCE ( AGENT, 0 ) + COALESCE ( AGENT2, 0 ) + COALESCE ( AGENT3, 0 )

  ) AS total,

  总和,  AGENTDATA

FROM   MaindB

HAVING   abs(total -总和)> 0.001;

这样显示出来的结果就很清楚,有几个的归一化有点小问题,改正就好了:

★上述例子中,这么多字段,一个一个手工输入很不合算,怎么弄出来呢?

在MySQL中执行SQL命令 Show Create Table MainDB; 结果就会生成这个表格的所有字段信息(可以用来再生成一个同样结构的表格).用替换功能将不必要的内容(如字段约束)除去, 再用Coalesce( 替换" `", 生成函数的左边部分,再用") +"替换"` ", 生成右边的部分,就可以得到大部分语句内容了,再手工整理一下就成了.

★MySQL中有一个非常棒的功能,使用REGEXP子句对字段进行正则表达式对比.

SELECT `配方名称`, `配方备注` from maindb where 配方备注 REGEXP "N(MC|CM)\\d{3}";

上一句搜索备注中,含有NMC或NCM并且后面跟三位数字(往往是523,613,811等)的字符串,用于找出正极材料说明的背景,就非常好用.但要注意的是,MySQL中常用的代表数字的分组在\d要表达成\\d, MySQL要吃掉一个\, 正则库也还要有一个\. 类似这种转义的情况其实在其它语言中也并不鲜见.

★如果MySQL中使用Decimal存放数值, 通过xlwings提取到Excel中进行计算时,要注意将Decimal类型转化成float类型再运算,避免精度下降. 

我碰到两次,MySQL中用Decimal(7,4)来存放百分比数值(数值7位,小数点后4位),发现到Excel中有些小数值丢失了.原因是我百分比数值除了100转化成实际的小数,导致小数位数增加而Decimal存放不了,结果后两位丢失了.先把MySQL中的Decimal转化成Float再处理就没有问题了. 另外Decimal与Float直接加减也不行,还是要转换为Float再减比较好.

以前在Access中一直不太明确的一些概念,通过对MySQL的学习,感觉更清楚.