★计算同一记录中不同字段之和
背景说明: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的学习,感觉更清楚.
