SQL查询问题

[h422274755]

有原始表
userid score createdate
1229 -25 2017-11-20 15:15:43.613
1229 -25 2017-11-20 15:25:25.723
1229 -10 2017-11-23 11:48:05.953
1229 10 2017-11-23 11:55:49.000
1229 -10 2017-11-26 16:16:10.040

得到结果表（满分为100分，score字段代表加扣分的数据）
userid score allscore createdate
1229 -25 75 2017-11-20 15:15:43.613
1229 -25 50 2017-11-20 15:25:25.723
1229 -10 40 2017-11-23 11:48:05.953
1229 10 50 2017-11-23 11:55:49.000
1229 -10 40 2017-11-26 16:16:10.040

求解决的SQL语句

[h422274755]

有没有人，在线等

[RINK_1]

select *,100+sub_total as allscore from table A
outer apply (select SUM(score) as sub_total from table where userid=A.userid and createdate<=A.createdate) as B

[xmniemaosheng]

突然想起powerbuilder的datawindow有个累计的函数，可惜SQL没有

[道素]

 ;WITH test(userid,score,createdate) AS (
    SELECT 1229	,-25,'2017-11-20 15:15:43.613' UNION ALL 
    SELECT 1229,-25,'2017-11-20 15:25:25.723' UNION ALL 
    SELECT 1229,-10,'2017-11-23 11:48:05.953' UNION ALL 
    SELECT 1229,10,'2017-11-23 11:55:49.000' UNION ALL 
    SELECT 1229,-10,'2017-11-26 16:16:10.040'
)
--sql server 2012+ 
--SELECT *,100+SUM(score)OVER(PARTITION BY userid ORDER BY t.createdate ) FROM test AS t
--before sql 2012
SELECT *,100+c.score
FROM test AS t
OUTER APPLY(SELECT SUM(score) AS score FROM test AS tt WHERE tt.userid=t.userid AND tt.createdate<=t.createdate) c

userid	score	createdate	(No column name)
1229	-25	2017-11-20 15:15:43.613	75
1229	-25	2017-11-20 15:25:25.723	50
1229	-10	2017-11-23 11:48:05.953	40
1229	10	2017-11-23 11:55:49.000	50
1229	-10	2017-11-26 16:16:10.040	40

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([userid] int,[score] int,[createdate] DATETIME)
Insert #T
select 1229,-25,N'2017-11-20 15:15:43.613' union all
select 1229,-25,N'2017-11-20 15:25:25.723' union all
select 1229,-10,N'2017-11-23 11:48:05.953' union all
select 1229,10,N'2017-11-23 11:55:49.000' union all
select 1229,-10,N'2017-11-26 16:16:10.040'
Go
--测试数据结束
SELECT  a.userid ,
        a.score ,
        ( SELECT    SUM(b.score) 
          FROM      #T b
          WHERE     a.[createdate] >= b.[createdate]
        )+ 100 AS allscore ,
        a.createdate
FROM    #T a

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t(userId INT,score INT,createdate datetime)
INSERT INTO t
          select 1229,-25,'2017-11-20 15:15:43.613'
union all select 1229,-25,'2017-11-20 15:25:25.723'
union all select 1229,-10,'2017-11-23 11:48:05.953'
union all select 1229,10,'2017-11-23 11:55:49.000'
union all select 1229,-10,'2017-11-26 16:16:10.040'

;WITH cte AS (
	SELECT *,ROW_NUMBER() OVER (PARTITION BY userId ORDER BY createdate) AS rid 
	FROM t	
)
SELECT userId,
score,
SUM(CASE WHEN rid=1 THEN 100+score ELSE score END) OVER(PARTITION BY userId ORDER BY createdate)  AS allscore
,createdate 
FROM cte
/*
userId	score	allscore	createdate
1229	-25	          75	2017-11-20 15:15:43.613
1229	-25	          50	2017-11-20 15:25:25.723
1229	-10	          40	2017-11-23 11:48:05.953
1229	10	          50	2017-11-23 11:55:49.000
1229	-10	          40	2017-11-26 16:16:10.040
*/

[不会VS]

SELECT s.userId ,s.Score ,100-s.Score AS allscore ,s.createdate FROM [表名] as s