# [原]用SQL做单位换算

pumeifen朋友在首页提出了一个问题“SQL 问题 求解”，我对这个问题延伸一下描述为“用数据库来做单位换算”，以长度单位为例，常用的长度单位有：毫米、厘米、分米等等，而英制的长度单位有英寸、英尺、码等，而我国传统的长度单位也有寸、尺、丈等等。

create table Length
(
name varchar2(50),
ratio number(10,5) not null,
parent varchar2(50),
constraints pk_length primary key (name)
)

insert into Length
---- 国际标准的长度单位
select '毫米' ,1.0 ,null from dual union all
select '厘米' ,10.0 ,'毫米' from dual union all
select '分米' ,10.0 ,'厘米' from dual union all
select   '米' ,10.0 ,'分米' from dual union all
select '千米' ,1000.0 ,'米' from dual union all
---- 我国传统的长度单位
select '寸' ,3.33,'厘米' from dual union all
select '尺' ,10.0 ,'寸' from dual union all
select '丈' ,10.0 ,'尺' from dual union all
---- 少数欧美国家使用的英制长度单位
select '英寸' , 2.54 , '厘米'  from dual union all
select '英尺' , 12.0  ,  '英寸'  from dual union all
select '码' , 3.0  ,  '英尺'  from dual union all
select '浪' , 220.0  ,  '码'  from dual union all
select '英寻' , 2.0  ,  '码'  from dual union all
select '英里' , 1760.0  ,  '码'  from dual 

SELECT name,ratio,parent
FROM length
CONNECT BY nocycle PRIOR parent = name
union all
select null,null,null from dual
union all
SELECT name,ratio,parent
FROM length
CONNECT BY nocycle PRIOR parent = name
START WITH name = '英里'
NAME            RATIO PARENT
---------- ---------- ----------

select
(     --英里转化成毫米
select exp(sum(ln(ratio)))
FROM length
CONNECT BY nocycle PRIOR  parent = name
)/(   --千米转化成毫米
select exp(sum(ln(ratio)))
FROM length
CONNECT BY nocycle PRIOR  parent = name
) "英里:千米"
from dual;
 英里:千米
----------
1.609344

WITH LengthTree
as
(
select name,ratio,parent,0 as level from Length
where name = '千米'
union all
select l.name,l.ratio,l.parent,level+1
from LengthTree t
inner join Length l
ON l.name=t.parent
)
select * from LengthTree

;WITH LengthTree
as
(
select name,ratio,parent,0 as level,name as start from Length
where name in ( '千米' , '英里' )
union all
select l.name,l.ratio,l.parent,level+1,t.start
from LengthTree t
inner join Length l
ON l.name=t.parent
)
select * from LengthTree
order by start,level

;WITH LengthTree
as
(
select name,ratio,parent,0 as level,name as start from Length
where name in ( '千米' , '英里' )
union all
select l.name,l.ratio,l.parent,level+1,t.start
from LengthTree t
inner join Length l
ON l.name=t.parent
)
select (
select exp(sum(log(ratio))) from LengthTree where start='英里'
)/(
select exp(sum(log(ratio))) from LengthTree where start='千米'
) as "英里:千米"

posted @ 2010-04-30 14:11  killkill  阅读(5252)  评论(10编辑  收藏  举报