看来是第二个了。昨天 xluzhong(打麻将一缺三,咋办?) 给了我很大帮助,不过我昨天是把问题简化了,其实Ratio(奖金比例)是根据积分来定的: 积分 Point 0-300 301-1000 1001-6000 6001-18000 大于18001 比例 Ratio 0% 3% 6% 9% 12% 我用你的语句时遇到问题, select id=identity(int,1,1),*, Ratio=cast(case when Point<301 then 0.00M when Point>300 and Point<1001 then 0.03M when Point>1000 and Point<6001 then 0.06M when Point>6000 and Point<18001 then 0.09M when Point>18000 then 0.12M end as decimal) ,Amount=(case when Amount not exists then 0 else Amount end) into #t from( select Accounts_Users.*,l=TempTable.[level] from Accounts_Users,TempTable,Accounts_Expenditure where Accounts_Users.UserID=TempTable.UserID and Accounts_Users.UserID=Accounts_Expenditure.UserID and ExpdMonth=month(getdate()) and [level]<3)t'M'附近有语法错误,'t'附近有语法错误
to: pbsh(业余的人受专业的伤),下面的语句已经通过了语法检查,是根据 xluzhong(打麻将一缺三,咋办?) 的语句改编的: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+Accounts_Users.UserID),7) as varchar(10)),TrueName,Points, 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) into #t from( select Accounts_Users.*,l=TempTable.[level] from Accounts_Users,TempTable,Accounts_Expenditure where Accounts_Users.UserID=TempTable.UserID and Accounts_Users.UserID=Accounts_Expenditure.UserID and ExpdMonth=month(getdate()) and [level]<3)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
SELECT ID=IDENTITY(INT,1,1),A.*, RATIO=CAST(CASE WHEN POINT<301 THEN 0 WHEN POINT>300 AND POINT<1001 THEN 30000 WHEN POINT>1000 AND POINT<6001 THEN 60000 WHEN POINT>6000 AND POINT<18001 THEN 90000 WHEN POINT>18000 THEN 120000 END AS DECIMAL), AMOUNT=CASE WHEN LEN(T.AMOUNT)=0 THEN 0 ELSE T.AMOUNT END INTO #T FROM (SELECT A.*,B.LEVEL FROM ACCOUNTS_USER A JOIN TEMPLTABLE B ON A.USERID=B.USERID JOIN EXPENDITURE C ON B.USERID=C.USERID WHERE C.EXPDMONTH=MONTH(GETDATE()) AND B.LEVEL<3) T
比例 Ratio 0% 3% 6% 9% 12%
我用你的语句时遇到问题,
select id=identity(int,1,1),*,
Ratio=cast(case when Point<301 then 0.00M
when Point>300 and Point<1001 then 0.03M
when Point>1000 and Point<6001 then 0.06M
when Point>6000 and Point<18001 then 0.09M
when Point>18000 then 0.12M
end as decimal)
,Amount=(case when Amount not exists then 0 else Amount end) into #t
from(
select Accounts_Users.*,l=TempTable.[level]
from Accounts_Users,TempTable,Accounts_Expenditure
where Accounts_Users.UserID=TempTable.UserID and Accounts_Users.UserID=Accounts_Expenditure.UserID and ExpdMonth=month(getdate()) and [level]<3)t'M'附近有语法错误,'t'附近有语法错误
但第一句好像也不准确。
Null是指存在这条记录,但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+Accounts_Users.UserID),7) as varchar(10)),TrueName,Points,
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)
into #t
from(
select Accounts_Users.*,l=TempTable.[level]
from Accounts_Users,TempTable,Accounts_Expenditure
where Accounts_Users.UserID=TempTable.UserID and Accounts_Users.UserID=Accounts_Expenditure.UserID and ExpdMonth=month(getdate()) and [level]<3)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
1 1 2005 3 200.00
2 3 2005 3 190.00
RATIO=CAST(CASE WHEN POINT<301 THEN 0
WHEN POINT>300 AND POINT<1001 THEN 30000
WHEN POINT>1000 AND POINT<6001 THEN 60000
WHEN POINT>6000 AND POINT<18001 THEN 90000
WHEN POINT>18000 THEN 120000 END AS DECIMAL),
AMOUNT=CASE WHEN LEN(T.AMOUNT)=0 THEN 0 ELSE T.AMOUNT END
INTO #T
FROM
(SELECT A.*,B.LEVEL
FROM ACCOUNTS_USER A
JOIN TEMPLTABLE B
ON A.USERID=B.USERID
JOIN EXPENDITURE C
ON B.USERID=C.USERID
WHERE C.EXPDMONTH=MONTH(GETDATE()) AND B.LEVEL<3) T
Accounts_Users表:
主键 UserID(int) FatherID(int) TrueName(Nvarchar) point(money) 1 0 田方 200.00
2 1 张三 300.00
Accounts_Expenditure表结构如下:ExpdID(int 主键) UserID(int) ExpdYear(int) ExpdMonth(int) Amount(money)
1 1 2005 3 200.00
2 3 2005 3 190.00