[转载][原]用SQL做单位换算 – killkill – 博客园.
pumeifen朋友在首页提出了一个问题“SQL 问题 求解”，我对这个问题延伸一下描述为“用数据库来做单位换算”，以长度单位为例，常用的长度单位 有：毫米、厘米、分米等等，而英制的长度单位有英寸、英尺、码等，而我国传统的长度单位也有寸、尺、丈等等。
小学时学习单位换算的基本 算法都是将他们转换成同一个长度单位然后再换算的，我借鉴这个算法写下此文。
首先在Oracle中构建一个长度单位的转换表：
4 |
ratio number(10,5) not null, |
6 |
constraints pk_length primary key (name) |
插入测试数据：
03 |
select '毫米' ,1.0 ,null from dual union all |
04 |
select '厘米' ,10.0 ,'毫米' from dual union all |
05 |
select '分米' ,10.0 ,'厘米' from dual union all |
06 |
select '米' ,10.0 ,'分米' from dual union all |
07 |
select '千米' ,1000.0 ,'米' from dual union all |
09 |
select '寸' ,3.33,'厘米' from dual union all |
10 |
select '尺' ,10.0 ,'寸' from dual union all |
11 |
select '丈' ,10.0 ,'尺' from dual union all |
13 |
select '英寸' , 2.54 , '厘米' from dual union all |
14 |
select '英尺' , 12.0 , '英寸' from dual union all |
15 |
select '码' , 3.0 , '英尺' from dual union all |
16 |
select '浪' , 220.0 , '码' from dual union all |
17 |
select '英寻' , 2.0 , '码' from dual union all |
18 |
select '英里' , 1760.0 , '码' from dual |
这个Length表实际上保存的是一棵树，简单表示如下：
如果，我想知道1英里=?千米，Oracle可以使用connect by …. start with 分别进行查询得从千米和英里到毫米之间的路径：
01 |
SELECT name,ratio,parent |
03 |
CONNECT BY nocycle PRIOR parent = name |
04 |
START WITH name = '千米' |
06 |
select null,null,null from dual |
08 |
SELECT name,ratio,parent |
10 |
CONNECT BY nocycle PRIOR parent = name |
11 |
START WITH name = '英里' |
02 |
---------- ---------- ---------- |
接下来只需要将千米和英里转换成毫米就可求比率了，SQL语句如下：
03 |
select exp(sum(ln(ratio))) |
05 |
CONNECT BY nocycle PRIOR parent = name |
06 |
START WITH name = '英里' |
08 |
select exp(sum(ln(ratio))) |
10 |
CONNECT BY nocycle PRIOR parent = name |
11 |
START WITH name = '千米' |
这里有个小插曲，SQL中没有计算累积的聚合函数，需要变换一下才能算到累积的结果，详细可以看我写的一篇博文[原] 计算乘积的聚合函数跑哪去了呢？
在SQL Server 2005/2008中，可以使用CTE的语法，以下省略在SQL Server中创建测试表Length的过程。
04 |
select name,ratio,parent,0 as level from Length |
07 |
select l.name,l.ratio,l.parent,level+1 |
12 |
select * from LengthTree |
稍微变换一下，我将英里、千米到毫米之间的路径列出来：
04 |
select name,ratio,parent,0 as level,name as start from Length |
05 |
where name in ( '千米' , '英里' ) |
07 |
select l.name,l.ratio,l.parent,level+1,t.start |
12 |
select * from LengthTree |
最后，就是再变换一下求解英里和千米之间的比率啦，SQL 如下：
04 |
select name,ratio,parent,0 as level,name as start from Length |
05 |
where name in ( '千米' , '英里' ) |
07 |
select l.name,l.ratio,l.parent,level+1,t.start |
13 |
select exp(sum(log(ratio))) from LengthTree where start='英里' |
15 |
select exp(sum(log(ratio))) from LengthTree where start='千米' |
看到这里，可能有朋友会问，如果我求1英里=?浪，比较好的做法是都转换成码然后再做运算，但是按照这个算法英里和浪都会最终转换成毫米再进行运 算，中间极有可能产生精度问题，而且运算量明显多很多。
是的，解决运算量的重点在于找到“千米路径”和“英里路径”的相交点，两条路径一旦相交，再往根节点“毫米”走下去的路径都是多余的（沿用上文，从 “千米”到“毫米”的路径称为“千米路径”，从“英里”到“毫米”的路径称为“英里路径”）。
虽然，通过集合的并、交、差可剔除多余的路径，但SQL变得非常长篇累赘，而且需要读取的块/页数不见减少，于是作罢，如果大家有兴趣，可以自己写 写，希望您有更好的解决方法。至于精度问题，我的意见是，不要偏太多就行了…….
希望本文对您有帮助。
长度单位主要参考百度百科的长度单位和wiki 的英制单位。
Mikel
