create table tbTest(baseCurrency nvarchar, Currency nvarchar,rt int)
insert tbTest
select 'RMB','USD',6 union all
select 'USD','EUR',2 union all
select 'RMB','JPY',1 union all
select 'RMB','HKD',2
baseCurrency /Currency = rt
从这个汇率表,我要得到 EUR / JPY 的值,怎么写函数或者存储过程。
insert tbTest
select 'RMB','USD',6 union all
select 'USD','EUR',2 union all
select 'RMB','JPY',1 union all
select 'RMB','HKD',2
baseCurrency /Currency = rt
从这个汇率表,我要得到 EUR / JPY 的值,怎么写函数或者存储过程。
解决方案 »
- SQL的EXEC不能嵌套怎么解决
- 存储过程的参数能否是数组?
- 求自动将SEOTags字段中的值插入Tags表的存储过程
- 疑问:sql server近期几乎每天固定时间(下午4点钟左右)卡住(即好象Enterprise都打不开),重新启动SQl server又恢复正常
- 数据库中表的字段更新的问题?
- 如何提出数据库中的重复数据?
- 求助,对于删除触发器来讲,怎么才能使它在判断单行删除时候才会触发,批量删除时不执行触发事件呢
- 为什么我的SQL SERVER用企业管理器无法连接,用查询分析器却可以?
- 求SQL,取出表A中第31到第40记录
- 请教各位VFP高手:VFP能否做后台数据库?能力和效率怎样?
- 菜鸟求助:请教sqlcmd用-i参数出错原因
- MS-SQL问题,请高手指点
额~~ 感觉有点递归意思,像部门和上级部门 那样,把route找出来,然后乘除。
但是又确实不仅是递归,求高手指点如何做上面效果
确实不属于递归了
insert tbTest
select 'RMB','USD',6 union all
select 'USD','EUR',2 union all
select 'RMB','JPY',1 union all
select 'RMB','HKD',2 union all
再插入Currency /baseCurrency 就可以得到除数和被除数的反。
select 'USD','RMB',1/6 union all
select 'EUR','USD',1/2 union all
select 'JPY','RMB',1 union all
select 'HKD','RMB',1/2 这样,我传入 一个 币种,就可以按照 从被除数 找除数这么1个递归思路。
估计可实现,请大家分析下我的思路对不对
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,name varchar(1),pid int)
insert into [tb]
select 1,'A',0 union all
select 2,'B',1 union all
select 3,'D',1 union all
select 4,'C',2 union all
select 5,'D',2 union all
select 6,'A',4 union all
select 7,'E',5 union all
select 8,'F',5
GO
;with cte
as
(
select *,[path]=cast([name]+'->' as varchar(100)) ,[level] = 1 from tb where pid = 0
union all
select a.*, cast(c.[path]+a.[name]+'->' as varchar(100)),[level]+1 from cte c ,tb a where a.pid = c.id
)
select
*
from cte
where len([path]) > 6 and right([path],3) = left([path],3)
/*
id name pid path level
----------- ---- ----------- -------------- -----
6 A 4 A->B->C->A-> 4(1 行受影响)
*/------------------------------------------------------------------------
-- Author : happyflystone
-- Date : 2010-04-06
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
Go
CREATE TABLE tb([cid] NVARCHAR(1),[pid] NVARCHAR(1))
Go
INSERT INTO tb
SELECT 'A','B' UNION ALL
SELECT 'A','D' UNION ALL
SELECT 'B','C' UNION ALL
SELECT 'B','D' UNION ALL
SELECT 'C','A' UNION ALL
SELECT 'D','E' UNION ALL
SELECT 'D','F'
GO
--Start
;with cte
as
(
select *,[path]=cast([cid]+'->' as varchar(100)) ,[level] = 1
from (select distinct cid,cast('' as nvarchar(1)) as pid from tb union select distinct pid ,'' from tb) b
union all
select a.*,cast(a.[cid]+'->'+c.[path] as varchar(100)),[level]+1
from cte c ,tb a
where a.pid = c.cid and charindex(a.[cid]+'->',c.[path])=0
)
select
[path]+cid+'->'
from cte
where exists(select 2 from tb where cid+'->' = right([path],3) and pid+'->' = left([path],3))-- = left([path],3)
--Result:
/*
--------------
A->B->C->A->
C->A->B->C->
B->C->A->B->(3 行受影响)*/
--End
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/06/5456223.aspx你可以看看
你的表的数据插入好像有点问题吧。
你的基准汇率不确定吗,一会是这个,一会又是哪个。
我觉得你这个需求,要么你就把所有可能的币种换算都存入数据库,需要哪两种的换算,就直接插入一条记录就可以了。
要么就是找一种货币作为基准,保存其他各种向这个基准的换算率,然后各种也可以换算了,都向基准换算。
你说的有道理的。 我现在实现了我的需求, 是在程序里做的递归,sql里不会写。 我就是要别人插入汇率表,然后 用这个汇率表去计算汇率, 人家爱怎么写怎么写,不管它每一条的基准是什么,当然一般情况都是以一个货币为基准。
if object_id('tbTest') is not null
drop table tbTest
go
create table tbTest(baseCurrency varchar(5), Currency varchar(5),rt int)
insert tbTest
select 'RMB','USD',6 union all
select 'USD','EUR',2 union all
select 'RMB','JPY',1 union all
select 'RMB','HKD',2
go
select * from tbTest
--以'RMB' = 1 为标准,则 'USD' 为 6 类推计算相关币别与'RMB'的之间倍率
create function dbo.showCurrency(@Currency varchar(5))
returns float
as
begin
declare @CurrencyRT float--定义一个返回当前币别与RMB之间的倍率
if @Currency = 'RMB'
select @CurrencyRT = 1 --RMB以1为准
else
begin --当不是'RMB'时候,则计算出以RMB为基准的倍率
declare @CurrencyTemp varchar(5)
select @CurrencyTemp = (select baseCurrency from tbTest where Currency = @Currency)
select @CurrencyRT = (select rt from tbTest where Currency = @Currency) * (dbo.showCurrency(@CurrencyTemp))--递归,调用函数本身,直到为RMB为止
end
return @CurrencyRT --返回值
endselect dbo.showCurrency('JPY')
go--主要是调用dbo.showCurrency()
create function dbo.ShowRt(@baseCurrency varchar(5),@Currency varchar(5))
returns float
as
begin
declare @rt float
select @rt = cast(dbo.showCurrency(@baseCurrency) as float) / dbo.showCurrency(@Currency)
return @rt
end
go
select 'EUR','JPY',dbo.showrt('EUR','JPY') as rt--结果
/*
baseCurrency Currency rt
EUR JPY 12
*/
--创建两个函数,用于计算表中的特定的汇率
if object_id('tbTest') is not null
drop table tbTest
go
create table tbTest(baseCurrency varchar(5), Currency varchar(5),rt int)
insert tbTest
select 'RMB','USD',6 union all
select 'USD','EUR',2 union all
select 'RMB','JPY',1 union all
select 'RMB','HKD',2
go
--select * from tbTest
--以'RMB' = 1 为标准,则 'USD' 为 6 类推计算相关币别与'RMB'的之间倍率
create function dbo.showCurrency(@Currency varchar(5))
returns float
as
begin
declare @CurrencyRT float--定义一个返回当前币别与RMB之间的倍率
if @Currency = 'RMB'
select @CurrencyRT = 1 --RMB以1为准
else
begin --当不是'RMB'时候,则计算出以RMB为基准的倍率
declare @CurrencyTemp varchar(5)
select @CurrencyTemp = (select baseCurrency from tbTest where Currency = @Currency)
select @CurrencyRT = (select rt from tbTest where Currency = @Currency) * (dbo.showCurrency(@CurrencyTemp))--递归,调用函数本身,直到为RMB为止
end
return @CurrencyRT --返回值
end--select dbo.showCurrency('JPY')
go--主要是调用dbo.showCurrency()
create function dbo.ShowRt(@baseCurrency varchar(5),@Currency varchar(5))
returns float
as
begin
declare @rt float
select @rt = ROUND(cast(dbo.showCurrency(@Currency) as float) / dbo.showCurrency(@baseCurrency),2 )
return @rt
end
go
select 'EUR','JPY',dbo.showrt('EUR','JPY') as rt--结果
/*
baseCurrency Currency rt
EUR JPY 0.08