34,597
社区成员
发帖
与我相关
我的任务
分享
with t as
(select Profit,profitSum,
rank() over(order by openTime) 'rn'
from syb
)
update a
set a.profitSum=
(select sum(b.Profit) from t b where b.rn<=a.rn)
from t a
[/quote]
好的我试试
with t as
(select Profit,profitSum,
rank() over(order by openTime) 'rn'
from syb
)
update a
set a.profitSum=
(select sum(b.Profit) from t b where b.rn<=a.rn)
from t a
with t as
(select Profit,profitSum,
row_number() over(order by openTime) 'rn'
from syb
)
update a
set a.profitSum=
(select sum(b.Profit) from t b where b.rn<=a.rn)
from t a
--drop table tb
create table tb
(
Profit decimal(10,2),
profitSum decimal(10,2)
)
insert into tb
select 20000.0,0.00 union all
select 5.00,0.00 union all
select 0.00,0.00 union all
select 0.00,0.00 union all
select -383.40,0.00 union all
select 379.80,0.00 union all
select 3.50,0.00
;with t
as
(
select *,
row_number() over(order by @@servername) as rownum
from tb
)
select profit,
(select sum(profit)
from t t2
where t2.rownum <= t1.rownum) as profitSum
from t t1
/*
profit profitSum
20000.00 20000.00
5.00 20005.00
0.00 20005.00
0.00 20005.00
-383.40 19621.60
379.80 20001.40
3.50 20004.90
*/
create table syb
(Profit decimal(10,2),profitSum decimal(10,2))
insert into syb
select 20000.0,0.00 union all
select 5.00,0.00 union all
select 0.00,0.00 union all
select 0.00,0.00 union all
select -383.40,0.00 union all
select 379.80,0.00 union all
select 3.50,0.00
with t as
(select Profit,profitSum,
row_number() over(order by getdate()) 'rn'
from syb
)
update a
set a.profitSum=
(select sum(b.Profit) from t b where b.rn<=a.rn)
from t a
select * from syb
/*
Profit profitSum
--------------------------------------- ---------------------------------------
20000.00 20000.00
5.00 20005.00
0.00 20005.00
0.00 20005.00
-383.40 19621.60
379.80 20001.40
3.50 20004.90
(7 row(s) affected)
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-18 17:04:15
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([profit] decimal(7,2),[profitsum] decimal(7,2))
insert [huang]
select 20000.00,0.00 union all
select 5.00,0.00 union all
select 0,0 union all
select -383.40,0 union all
select 379.80,0 union all
select 3.50,0
--------------开始查询--------------------------
;WITH cte AS (
select CONVERT( decimal(7,2),profit)profit,CONVERT( decimal(7,2),profitsum) AS profitsum,ROW_NUMBER() OVER(ORDER BY GETDATE())id
from [huang]
)
SELECT profit,(SELECT SUM(profit)profitsum FROM cte a WHERE a.id<=cte.id)
FROM cte
----------------结果----------------------------
/*
profit
--------------------------------------- ---------------------------------------
20000.00 20000.00
5.00 20005.00
0.00 20005.00
-383.40 19621.60
379.80 20001.40
3.50 20004.90
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-10-18 17:09:30
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([profit] numeric(7,2),[profitsum] numeric(3,2))
insert [tb]
select 20000.0,0.00 union all
select 5.00,0.00 union all
select 0.00,0.00 union all
select 0.00,0.00 union all
select -383.40,0.00 union all
select 379.80,0.00 union all
select 3.50,0.00
--------------开始查询--------------------------
;with f as
(select id=row_number()over(order by getdate()),* from tb)
select
profit,
(select sum(profit) as profitsum from f where id<=t.id) as profitsum
from
f t
----------------结果----------------------------
/* profit profitsum
--------------------------------------- ---------------------------------------
20000.00 20000.00
5.00 20005.00
0.00 20005.00
0.00 20005.00
-383.40 19621.60
379.80 20001.40
3.50 20004.90
(7 行受影响)
*/