with t(timestr,itemid,[user],amountt) as( select [time],itemid,[user],min(cast(amount as int)) from itemusetmp group by [time],itemid,[user] union all select p.[time] ,p.itemid,p.[user],convert(int,p.amount+e.amountt) from itemusetmp p , t e where p.[itemid]=e.itemid and p.[user]=e.[user] and p.[time]>e.[timestr] )
with t(timestr,itemid,[user],amountt) as( select [time],itemid,[user],min(cast(amount as int)) from itemusetmp group by [time],itemid,[user] union all select p.[time] ,p.itemid,p.[user],convert(int,p.amount+e.amountt) from itemusetmp p , t e where p.[itemid]=e.itemid and p.[user]=e.[user] and p.[time]>e.[timestr] )
大数据量的递归用with语句不太合适,试试改成其他方式
select a.[time],a.itemid,a.[user],b.[amount] from itemusetmp a, (select max([time]) 'time',sum(amount) 'amount',itemid,[user] from itemusetmp group by itemid,[user]) b where a.itemid=b.itemid and a.[user]=b.[user]
兄弟,这个语句不行,只能求出最大的,而不是求每次购买后,使用完道具的时间。 道具 购买数量 购买时间 A 10 1 A 3 12 道具 使用数量 使用时间 A 1 2 A 1 3 A 1 4 A 1 5 A 1 6 A 1 7 A 1 8 A 1 9 A 1 10 A 1 11 A 1 13 A 1 14 A 1 15 想要的结果是 道具 购买数量 购买时间 使用完时的时间 A 10 1 11 A 10 12 15
好像不用cte 去递归吧你试试下面这个语句 select a.[time],a.itemid,a.[user],b.[amount], amount=( select sum(amount) from itemusetmp b WHERE a.itemid=b.itemid and a.[user]=b.[user] AND b.[time]<=a.[time] ) from itemusetmp a
试试这个: with t as (select *,ROW_NUMBER() over(partition by [user],[itemid] order by [time]) as rn from [itemusetmp]) select a.rn,a.[time],a.[user],a.[itemid],a.[amount],a.[amount]+isnull(SUM(b.amount),0) as amountt From t a left join t b on a.[user]=b.[user] and a.itemid=b.itemid and a.rn>b.rn group by a.rn,a.[time],a.[user],a.[itemid],a.[amount] order by a.[user],a.[itemid],a.[time]
with t(timestr,itemid,[user],amountt)
as(
select [time],itemid,[user],min(cast(amount as int)) from itemusetmp group by [time],itemid,[user]
union all
select p.[time] ,p.itemid,p.[user],convert(int,p.amount+e.amountt) from itemusetmp p , t e
where p.[itemid]=e.itemid and p.[user]=e.[user] and p.[time]>e.[timestr]
)
as(
select [time],itemid,[user],min(cast(amount as int)) from itemusetmp group by [time],itemid,[user]
union all
select p.[time] ,p.itemid,p.[user],convert(int,p.amount+e.amountt) from itemusetmp p , t e
where p.[itemid]=e.itemid and p.[user]=e.[user] and p.[time]>e.[timestr]
)
(select max([time]) 'time',sum(amount) 'amount',itemid,[user] from itemusetmp group by itemid,[user]) b
where a.itemid=b.itemid and a.[user]=b.[user]
道具 购买数量 购买时间
A 10 1
A 3 12
道具 使用数量 使用时间
A 1 2
A 1 3
A 1 4
A 1 5
A 1 6
A 1 7
A 1 8
A 1 9
A 1 10
A 1 11
A 1 13
A 1 14
A 1 15
想要的结果是
道具 购买数量 购买时间 使用完时的时间
A 10 1 11
A 10 12 15
好像不用cte 去递归吧你试试下面这个语句
select a.[time],a.itemid,a.[user],b.[amount],
amount=(
select sum(amount) from itemusetmp b WHERE a.itemid=b.itemid and a.[user]=b.[user] AND b.[time]<=a.[time]
)
from itemusetmp a
with t as
(select *,ROW_NUMBER() over(partition by [user],[itemid] order by [time]) as rn from [itemusetmp])
select a.rn,a.[time],a.[user],a.[itemid],a.[amount],a.[amount]+isnull(SUM(b.amount),0) as amountt From t a left join t b
on a.[user]=b.[user] and a.itemid=b.itemid and a.rn>b.rn
group by a.rn,a.[time],a.[user],a.[itemid],a.[amount]
order by a.[user],a.[itemid],a.[time]