;with cte(id,client,num,price) as ( select 1,'aaa',1,100 union all select 2,'aaa',2,200 union all select 3,'bbb',1,200 union all select 4,'bbb',4,130 union all select 5,'dddd',1,140 union all select 6,'dddd',2,150 union all select 7,'aaa',3,160 )select *,累加金额=(select SUM(num*price) from cte b where a.client=b.client and b.id<=a.id) from cte a order by client,id/* id client num price 累加金额 1 aaa 1 100 100 2 aaa 2 200 500 7 aaa 3 160 980 3 bbb 1 200 200 4 bbb 4 130 720 5 dddd 1 140 140 6 dddd 2 150 440 */
select 客户名称,sum(cast(数量*单价*0.06 as decimal(12,2))) as 返利金额 from 表 group by 客户名称
select 客户名称,sum(cast(cast(数量*单价 as decimal(12,2))*0.06 as decimal(12,2))) as 返利金额 from 表 group by 客户名称
select 售点名称,sum(预订量*单价*0.06) as 返利金额 from 表 group by 售点名称
;with cte(售点名称,预订量,单价) as ( select '朗通圣之火网吧',3,42 union all select '朗通圣之火网吧',8,55 union all select '朗通圣之火网吧',5,32 union all select '朗通圣之火网吧',4,18 union all select '朗通圣之火网吧',3,11 union all select '朗通澳海源',2.5,42 union all select '朗通澳海源',4,55 ), cte2 as ( select *,rn=ROW_NUMBER() over(order by getdate()) from cte )select 售点名称,预订量,单价, 累计金额=(select SUM(预订量*单价) from cte2 b where a.售点名称=b.售点名称 and b.rn<=a.rn) from cte2 a/* 售点名称 预订量 单价 累计金额 朗通圣之火网吧 3.0 42 126.0 朗通圣之火网吧 8.0 55 566.0 朗通圣之火网吧 5.0 32 726.0 朗通圣之火网吧 4.0 18 798.0 朗通圣之火网吧 3.0 11 831.0 朗通澳海源 2.5 42 105.0 朗通澳海源 4.0 55 325.0 */
select 售点名称,sum(预订量*单价*0.06) as 返利金额 from Table1 group by 售点名称
--drop table tbCREATE TABLE TB( 售点名称 nvarchar(20), 预订量 Numeric(20,2), 单价 numeric(20,2) ) insert into tb select '朗通圣之火网吧',3,42 union all select '朗通圣之火网吧',8,55 union all select '朗通圣之火网吧',5,32 union all select '朗通圣之火网吧',4,18 union all select '朗通圣之火网吧',3,11 union all select '朗通澳海源',2.5,42 union all select '朗通澳海源',4,55;with t as ( select *, ROW_NUMBER() over(order by @@servername) as rownum from tb )select t1.售点名称, t1.预订量, t1.单价, (select SUM(t2.预订量*t2.单价) from t t2 where t1.售点名称 = t2.售点名称 and t1.rownum >= t2.rownum)as 累计金额 from t t1 /* 售点名称 预订量 单价 累计金额 朗通圣之火网吧 3.00 42.00 126.0000 朗通圣之火网吧 8.00 55.00 566.0000 朗通圣之火网吧 5.00 32.00 726.0000 朗通圣之火网吧 4.00 18.00 798.0000 朗通圣之火网吧 3.00 11.00 831.0000 朗通澳海源 2.50 42.00 105.0000 朗通澳海源 4.00 55.00 325.0000 */
from t
(
select 1,'aaa',1,100
union all select 2,'aaa',2,200
union all select 3,'bbb',1,200
union all select 4,'bbb',4,130
union all select 5,'dddd',1,140
union all select 6,'dddd',2,150
union all select 7,'aaa',3,160
)select *,累加金额=(select SUM(num*price) from cte b where a.client=b.client and b.id<=a.id)
from cte a
order by client,id/*
id client num price 累加金额
1 aaa 1 100 100
2 aaa 2 200 500
7 aaa 3 160 980
3 bbb 1 200 200
4 bbb 4 130 720
5 dddd 1 140 140
6 dddd 2 150 440
*/
select 客户名称,sum(cast(数量*单价*0.06 as decimal(12,2))) as 返利金额
from 表
group by 客户名称
select 客户名称,sum(cast(cast(数量*单价 as decimal(12,2))*0.06 as decimal(12,2))) as 返利金额
from 表
group by 客户名称
select 售点名称,sum(预订量*单价*0.06) as 返利金额
from 表
group by 售点名称
(
select '朗通圣之火网吧',3,42
union all select '朗通圣之火网吧',8,55
union all select '朗通圣之火网吧',5,32
union all select '朗通圣之火网吧',4,18
union all select '朗通圣之火网吧',3,11
union all select '朗通澳海源',2.5,42
union all select '朗通澳海源',4,55
),
cte2 as
(
select *,rn=ROW_NUMBER() over(order by getdate()) from cte
)select 售点名称,预订量,单价,
累计金额=(select SUM(预订量*单价) from cte2 b where a.售点名称=b.售点名称 and b.rn<=a.rn)
from cte2 a/*
售点名称 预订量 单价 累计金额
朗通圣之火网吧 3.0 42 126.0
朗通圣之火网吧 8.0 55 566.0
朗通圣之火网吧 5.0 32 726.0
朗通圣之火网吧 4.0 18 798.0
朗通圣之火网吧 3.0 11 831.0
朗通澳海源 2.5 42 105.0
朗通澳海源 4.0 55 325.0
*/
select 售点名称,sum(预订量*单价*0.06) as 返利金额
from Table1
group by 售点名称
--drop table tbCREATE TABLE TB(
售点名称 nvarchar(20),
预订量 Numeric(20,2),
单价 numeric(20,2)
) insert into tb
select '朗通圣之火网吧',3,42
union all select '朗通圣之火网吧',8,55
union all select '朗通圣之火网吧',5,32
union all select '朗通圣之火网吧',4,18
union all select '朗通圣之火网吧',3,11
union all select '朗通澳海源',2.5,42
union all select '朗通澳海源',4,55;with t
as
(
select *,
ROW_NUMBER() over(order by @@servername) as rownum
from tb
)select t1.售点名称,
t1.预订量,
t1.单价,
(select SUM(t2.预订量*t2.单价)
from t t2
where t1.售点名称 = t2.售点名称
and t1.rownum >= t2.rownum)as 累计金额
from t t1
/*
售点名称 预订量 单价 累计金额
朗通圣之火网吧 3.00 42.00 126.0000
朗通圣之火网吧 8.00 55.00 566.0000
朗通圣之火网吧 5.00 32.00 726.0000
朗通圣之火网吧 4.00 18.00 798.0000
朗通圣之火网吧 3.00 11.00 831.0000
朗通澳海源 2.50 42.00 105.0000
朗通澳海源 4.00 55.00 325.0000
*/