34,590
社区成员
发帖
与我相关
我的任务
分享数据统计的问题
有2个字段，Profit, profitSum。默认profitSum的值为0。如下图
现在要做下统计，规则第一条profitSum的值就为Profit
第二条profitSum的值为第一条的profitSum+第二条的Profit
第三条profitSum的值为第二条的profitSum+第三条的Profit
…………………………
以此类推
最后的结果为
这样的SQL语句怎么写？？
现在要做下统计，规则第一条profitSum的值就为Profit
第二条profitSum的值为第一条的profitSum+第二条的Profit
第三条profitSum的值为第二条的profitSum+第三条的Profit
…………………………
以此类推
最后的结果为
这样的SQL语句怎么写？？
...全文
请发表友善的回复…
发表回复
t101lian 2013-10-22
- 打赏
- 举报
如果用 exists 应该怎么写呢
火拼阿三 2013-10-22
- 打赏
- 举报
学习了。新的方法。。
guan_tu 2013-10-21
- 打赏
- 举报
尼玛，都是大神哪!
棉花棒棒糖 2013-10-21
- 打赏
- 举报
参考一下这个贴子
http://bbs.csdn.net/topics/390110662
唐诗三百首 2013-10-21
- 打赏
- 举报
请试试13楼的代码.
sy_binbin 2013-10-21
- 打赏
- 举报
这是我本地测试数据，从第三条数据开始，时间是一样的，但profitSum自动的值也是一样的了！！再帮我调试调试吧
sy_binbin 2013-10-21
- 打赏
- 举报
try this,
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
唐诗三百首 2013-10-21
- 打赏
- 举报
try this,
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
唐诗三百首 2013-10-21
- 打赏
- 举报
try this,
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
發糞塗牆 2013-10-21
- 打赏
- 举报
;WITH cte AS (
select CONVERT( decimal(7,2),profit)profit,CONVERT( decimal(7,2),profitsum) AS profitsum,ROW_NUMBER() OVER(ORDER BY openTime)id
from [huang]
)
SELECT profit,(SELECT SUM(profit)profitsum FROM cte a WHERE a.id<=cte.id)
FROM cte
sy_binbin 2013-10-21
- 打赏
- 举报
补充下，时间有可能还会是一样的
--小F-- 2013-10-21
- 打赏
- 举报
把ORDER BY GETDATE()改成 ORDER BY openTime
發糞塗牆 2013-10-21
- 打赏
- 举报
给出数据，最好给文本形式的
sy_binbin 2013-10-21
- 打赏
- 举报
如果我再加一个openTime字段，数据类型的时间，时间是任意时间。
我想按照时间升序排序，实现的效果和图片上的一样。这样SQL语句该怎么写啊？
LongRui888 2013-10-18
- 打赏
- 举报
--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
*/
-Tracy-McGrady- 2013-10-18
- 打赏
- 举报
好东西，我以前只会使用循环累加的办法，谢谢了
唐诗三百首 2013-10-18
- 打赏
- 举报
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)
*/
發糞塗牆 2013-10-18
- 打赏
- 举报
我开始写的时候明明没人回复的....發糞塗牆 2013-10-18
- 打赏
- 举报
----------------------------------------------------------------
-- 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
*/
--小F-- 2013-10-18
- 打赏
- 举报
----------------------------------------------------------------
-- 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 行受影响)
*/加载更多回复（1）
