楼主,这样解决吧。
Amount还是用Amount=isnull(Amount,0) If Exists(条件)
Begin
......
End
Else
写一条在临时表#t中插入一个0值Amount的SQL语句
Amount还是用Amount=isnull(Amount,0) If Exists(条件)
Begin
......
End
Else
写一条在临时表#t中插入一个0值Amount的SQL语句
调试欢乐多
@UserID int
as
create table TempTable(UserID int,[level] int)
declare @level int
set @level=0
insert TempTable select @UserID,@level
while @@rowcount>0
begin
set @level=@level+1
insert TempTable select Accounts_Users.UserID,@level
from Accounts_Users,TempTable
where Accounts_Users.FatherID=TempTable.UserID and TempTable.[level]=@level-1
end select id=identity(int,1,1),UserNo=cast(right((10000000+UserID),7) as varchar(10)),*,
Ratio=case when Point<301 then 0
when Point>300 and Point<1001 then 0.03
when Point>1000 and Point<6001 then 0.06
when Point>6000 and Point<18001 then 0.09
when Point>18000 then 0.12
end,Amount=isnull(Amount,0) --(case when Amount not exists then 0 else Amount end)
into #t
from(
select Accounts_Users.*,l=TempTable.[level]
from Accounts_Users
join TempTable on Accounts_Users.UserID=TempTable.UserID and [level]<3
left join Accounts_Expenditure on Accounts_Users.UserID=Accounts_Expenditure.UserID and ExpdMonth=month(getdate())
)t
update #t
set ratio=case when l=0 then (select ratio from #t where id=1)-0
else (select ratio from #t where id=1)-Accounts_Users.ratio
end
from #t Accounts_Users
update #t
set ratio=0.03 from #t where ratio=0
select * from #t
select sum(ratio*amount)/100
from #t
drop table #t
drop table TempTable
显示:Ado错误,列名 ' Amount' 无效。
@UserID int
as
create table TempTable(UserID int,[level] int)
declare @level int
set @level=0
insert TempTable select @UserID,@level
while @@rowcount>0
begin
set @level=@level+1
insert TempTable select Accounts_Users.UserID,@level
from Accounts_Users,TempTable
where Accounts_Users.FatherID=TempTable.UserID and TempTable.[level]=@level-1
end select id=identity(int,1,1),UserNo=cast(right((10000000+UserID),7) as varchar(10)),*,
Ratio=case when Point<301 then 0
when Point>300 and Point<1001 then 0.03
when Point>1000 and Point<6001 then 0.06
when Point>6000 and Point<18001 then 0.09
when Point>18000 then 0.12
end,Amount=isnull(Amount,0) --(case when Amount not exists then 0 else Amount end)
into #t
from(
select Accounts_Users.*,l=TempTable.[level] ,Amount
from Accounts_Users
join TempTable on Accounts_Users.UserID=TempTable.UserID and [level]<3
left join Accounts_Expenditure on Accounts_Users.UserID=Accounts_Expenditure.UserID and ExpdMonth=month(getdate())
)t
update #t
set ratio=case when l=0 then (select ratio from #t where id=1)-0
else (select ratio from #t where id=1)-Accounts_Users.ratio
end
from #t Accounts_Users
update #t
set ratio=0.03 from #t where ratio=0
select * from #t
select sum(ratio*amount)/100
from #t
drop table #t
drop table TempTable
是代表什么意思?
ALTER PROCEDURE up_Accounts_GetBonusByUserID
@UserID int
as
create table #TempTable(UserID int,[level] int)
declare @level int
set @level=0
insert #TempTable select @UserID,@level
while @@rowcount>0
begin
set @level=@level+1
insert #TempTable select Accounts_Users.UserID,@level
from Accounts_Users,#TempTable
where Accounts_Users.FatherID=#TempTable.UserID and #TempTable.[level]=@level-1
end select id=identity(int,1,1),UserNo=cast(right((10000000+UserID),7) as varchar(10)),TrueName,Point,
Ratio=case when Point<301 then 0
when Point>300 and Point<1001 then 0.03
when Point>1000 and Point<6001 then 0.06
when Point>6000 and Point<18001 then 0.09
when Point>18000 then 0.12
end,ILevel,Amount=isnull(Amount,0) --(case when Amount not exists then 0 else Amount end)
into #t
from(
select Accounts_Users.*,ILevel=#TempTable.[level] ,Amount
from Accounts_Users
join #TempTable on Accounts_Users.UserID=#TempTable.UserID and [level]<3
left join Accounts_Expenditure on Accounts_Users.UserID=Accounts_Expenditure.UserID and ExpdMonth=month(getdate())
)t
update #t
set ratio=case when ILevel=0 then (select ratio from #t where id=1)-0
else (select ratio from #t where id=1)-Accounts_Users.ratio
end
from #t Accounts_Users
update #t
set ratio=0.03 from #t where ratio=0
select UserNo,TrueName,Point,Ratio,ILevel,Amount from #t
select sum(ratio*amount)/100
from #t
drop table #TempTable