update tableB
set fee=ta.sumfee,lastmonthfee=tb.sumfee,
riserate=(case when tb.sumfee>0 then (ta.sumfee-tb.sumfee)/tb.sumfee else 0 end)
from tableB left join (select product,month,sum(fee) as sumfee
from tableA
group by product,month) ta
on tableB.product=ta.product and tableB.month=ta.month
left join (select product,month,sum(fee) as sumfee
from tableA
group by product,month) tb
on tableB.product=ta.product and tableB.month=ta.month+1未测试的。
set fee=ta.sumfee,lastmonthfee=tb.sumfee,
riserate=(case when tb.sumfee>0 then (ta.sumfee-tb.sumfee)/tb.sumfee else 0 end)
from tableB left join (select product,month,sum(fee) as sumfee
from tableA
group by product,month) ta
on tableB.product=ta.product and tableB.month=ta.month
left join (select product,month,sum(fee) as sumfee
from tableA
group by product,month) tb
on tableB.product=ta.product and tableB.month=ta.month+1未测试的。
还是两步实现吧!
能不能用简单一点的语句直接计算出上月费用???
然后再update tableB set riserate=(lastmonthfee-fee)/fee
select product,month,sum(fee) as fee,isnull((select sum(fee) from tb where product=a.product and [month]=a.[month]-1),0) as lastmonthfee from tb a group by product,[month] ) b
select product,month,fee,lastmonthfee,(case when lastmonthfee<>0 then ((fee-lastmonthfee)/lastmonthfee)*100 else null end) as riserate from (
select product,month,sum(fee) as fee,isnull((select sum(fee) from tb where product=a.product and [month]=a.[month]-1),0) as lastmonthfee from tb a group by product,[month] ) b
update tableB set fee=b.fee,lastmonthfee=b.lastmonthfee,riserate=(case when lastmonthfee<>0 then ((fee-lastmonthfee)/lastmonthfee)*100 else null end) from tableB,(
select product,month,sum(fee) as fee,isnull((select sum(fee) from tb where product=a.product and [month]=a.[month]-1),0) as lastmonthfee from tb a group by product,[month] ) b where tableB.product=b.product and tableB.month=b.month
两个问题:
1、每个品种每个月都一定有销售吗?是的,我知道一般说来都是的,但你能肯定吗?
2、12月的下一个月是1月吗?如何表达?--这似乎直接涉及到表的设计。
2、12月的下一个月是1月吗?如何表达?--这似乎直接涉及到表的设计。
确实也存在这个问题,我还是没有好好考虑!!!
这里重要是计算上月的费用,当然了也就是1月的上月应该是12月!!!
太痛苦了
或加一个YEAR列?
你这条语句能执行,但结果不对!您能再帮我测试一下吗???
update tableB
set fee=ta.sumfee,lastmonthfee=tb.sumfee,
riserate=(case when tb.sumfee>0 then (ta.sumfee-tb.sumfee)/tb.sumfee else 0 end)
from tableB left join (select product,month,sum(fee) as sumfee
from tableA
group by product,month) ta
on tableB.product=ta.product and tableB.month=ta.month
left join (select product,month,sum(fee) as sumfee
from tableA
group by product,month) tb
on tableB.product=ta.product and tableB.month=tb.month+1
~~~~ -->改成tb另:不知道你的fee 字段是什么类型,若是int, 可能得不到正确的增长率。
若是int,写成:
update tableB
set fee=ta.sumfee,lastmonthfee=tb.sumfee
--,riserate=(ta.sumfee-tb.sumfee)
,riserate=(case when tb.sumfee<>0 then cast((ta.sumfee-tb.sumfee) as float)/tb.sumfee else 0 end)
from tableB left join (select product,month,sum(fee) as sumfee
from tableA
group by product,month) ta
on tableB.product=ta.product and tableB.month=ta.month
left join (select product,month,sum(fee) as sumfee
from tableA
group by product,month) tb
on tableB.product=ta.product and tableB.month=tb.month+1因为还是不知道你的跨年度处理,所以上面的写法也没有做处理。你自己看该怎么办,再改改。
update tableB
set fee=ta.sumfee,lastmonthfee=tb.sumfee
,riserate=(case when tb.sumfee<>0 then cast((ta.sumfee-tb.sumfee) as float)/tb.sumfee else 0 end)
from tableB left join (select product,month,sum(fee) as sumfee
from tableA
group by product,month) ta
on tableB.product=ta.product and tableB.month=ta.month
left join (select product,month,sum(fee) as sumfee
from tableA
group by product,month) tb
on tableB.product=ta.product and tableB.month=tb.month+1
:j9988(j9988)遇到你们这么的朋友,我真是感到很幸运!谢谢你们的热情帮助。
这个问题我基本上调通了,用的是j9988(j9988)的最后一句,不过riserate需要改一改,改完后如下:
update tableb
set fee=b.fee,lastmonthfee=b.lastmonthfee,
riserate=(case when b.lastmonthfee<>0
then ((b.fee-b.lastmonthfee)/b.lastmonthfee)*100
else null
end)
from tableb,(select product,month,sum(fee) as fee,
isnull((select sum(fee)
from tableb
where product=a.product and [month]=a.[month]-1),0) as lastmonthfee
from tableb a
group by product,[month]) b where tableB.product=b.product and tableB.month=b.month
供以后用到这块的朋友做个参考吧!
icevi(按纽工厂)朋友的最后一句我还没调通,我再调试一下,我觉得应该也没有问题。
真的非常感谢你们,谢谢!!!
INSERT INTO tableb (product, mth, fee)
SELECT product, mth, SUM(fee) FROM tablea
WHERE product in ('desk', 'note')
GROUP BY product, mth
ORDER BY product ,mth DESC--生成游标
DECLARE tableb_cur CURSOR FOR
SELECT * FROM tableb
FOR UPDATE OF lastmonthfee, riserate--计算lastmonthfee和riserate
OPEN tableb_cur
DECLARE @fetch_status INT
DECLARE @product char(10), @mth int, @fee float, @lastmonthfee float, @riserate floatFETCH NEXT FROM tableb_cur INTO @product, @mth, @fee, @lastmonthfee, @riserate
SELECT @fetch_status = @@FETCH_STATUS
WHILE @fetch_status = 0
BEGIN
SELECT @lastmonthfee = fee FROM tableb WHERE product = @product and mth = @mth - 1
if(@lastmonthfee < 0.00001)
SELECT @riserate = 0
ELSE
SELECT @riserate = (@fee-@lastmonthfee)/@lastmonthfee
UPDATE tableb SET lastmonthfee = @lastmonthfee, riserate = @riserate WHERE CURRENT OF tableb_cur
FETCH NEXT FROM tableb_cur INTO @product, @mth, @fee, @lastmonthfee, @riserate
SELECT @fetch_status = @@FETCH_STATUS
END
CLOSE tableb_cur
--释放游标
deallocate tableb_cur
GO
--注:未考虑跨年份的问题