create table #tablea(uid int,root int,rate float)
go
insert into #tablea select
8001, 0 , 1 union all select
8002 , 8001 , 1.2 union all select
8003 , 8002, 1.1
go
create procedure d_test
@uid int
as
declare @table table(uid int,root int,rate float)
insert into @table select uid,root,rate from #tablea where uid=@uid
while @@rowcount>0
begin
insert into @table select a.uid,a.root,a.rate from #tablea a
join @table b on a.uid=b.root and a.uid not in (select uid from @table)
end
declare @s varchar(8000)
set @s=''
select @s=@s+'*'+cast(rate as varchar) from @table
set @s='select '+stuff(@s,1,1,'')
print @s
exec(@s)
goexec d_test 8003/*(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)select 1.1*1.2*1
---------
1.32(1 row(s) affected)
*/
go
insert into #tablea select
8001, 0 , 1 union all select
8002 , 8001 , 1.2 union all select
8003 , 8002, 1.1
go
create procedure d_test
@uid int
as
declare @table table(uid int,root int,rate float)
insert into @table select uid,root,rate from #tablea where uid=@uid
while @@rowcount>0
begin
insert into @table select a.uid,a.root,a.rate from #tablea a
join @table b on a.uid=b.root and a.uid not in (select uid from @table)
end
declare @s varchar(8000)
set @s=''
select @s=@s+'*'+cast(rate as varchar) from @table
set @s='select '+stuff(@s,1,1,'')
print @s
exec(@s)
goexec d_test 8003/*(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)select 1.1*1.2*1
---------
1.32(1 row(s) affected)
*/
解决方案 »
- 请教一个关于inner join 和select top 1的sql语句
- 求一SQL语句?
- 关于sp_addlinkedserver的问题:建立进程出错!
- 存储过程的植的接受过程中遇到难题了,请帮忙,谢谢
- sql 查询两张表中,结果中询重复记录,只显示一条
- 一个存储过程的出错问题
- selecl语句存在一个变量里,想要在Transact SQL中,得到这条SQL语句的结果放到一个变量
- 简单问题.
- yangzi :关于job和dbms的帖子为什么都找不到了
- 怎样用html, javascript 写一个最最最最最最基础的网站啊 跪求
- 两个表的字段完全相同,如何简洁的把一个表中的一个记录插入(转入)到另一个表中!!
- Case判断??
CREATE FUNCTION dbo.Fun_GetTask_Root(@cTaskID CHAR(12))
RETURNS CHAR(12)
AS
BEGIN
DECLARE @Id CHAR(12)
SELECT @Id=TSK_PID
FROM TSK_TASK
WHERE TSK_ID=@cTaskID IF LTRIM(RTRIM(@Id))='' OR @Id=NULL
RETURN @cTaskID
SET @Id=dbo.Fun_GetTask_Root(@Id)
RETURN @Id
END
create table A(
uid int,
root int,
rate numeric(10,9)
)
insert into A select 8001 ,0 ,1
insert into A select 8002 ,8001 ,1.2
insert into A select 8003 ,8002 ,1.1
--创建函数
create function F_rate(@uid int)
returns numeric(10,9)
as
begin
declare @rate numeric(10,9)
declare @root int
select @root = root ,@rate = rate from A where uid = @uid
while exists(select 1 from A where uid = @root)
begin
set @uid = @root
select @root = root ,@rate = @rate*rate from A where uid = @uid
end
return @rate
end
--执行查询
select *,dbo.f_rate(uid) from a
--返回结果
8001 0 1.000000000 1.000000000
8002 8001 1.200000000 1.200000000
8003 8002 1.100000000 1.320000000
CREATE FUNCTION dbo.Fun_GetTask_Root(@cTaskID CHAR(12))
RETURNS CHAR(12)
AS
BEGIN
DECLARE @Id CHAR(12)
SELECT @Id=TSK_PID
FROM TSK_TASK
WHERE TSK_ID=@cTaskID IF LTRIM(RTRIM(@Id))='' OR @Id=NULL
RETURN @cTaskID
SET @Id=dbo.Fun_GetTask_Root(@Id)
RETURN @Id
END
goinsert into test
select '11','0',1.2
union
select '22','11',1.1
union
select '33','22',2.0
--select * from test
go
alter function GetRate(@str varchar(255))
RETURNS float(2)
as
begin
declare @result float(2)
select @result=cc from test where bb=rtrim(@str)
if rtrim(@str)<>'0'
begin
while exists (select * from test where aa=rtrim(@str))
begin
select @result=@result*cc,@str=bb from test where aa=rtrim(@str)
end
end
return(@result)
end
go
print dbo.GetRate('22')
go
drop table test