求一条sql

[cb1156]

id accountId date point
1 1 2017-05-29 16:27:38 1
2 1 2017-05-30 16:27:48 -1
3 1 2017-05-31 16:28:06 1
4 2 2017-05-29 16:33:01 1
5 2 2017-05-30 16:33:22 -1


日期和id递增。。
要每个用户最后一次 负积分 之后的积分 求和

即结果
accountId sumPoint
1 1
2 0

[zjcxc]

select accountId  , sum(point) as sumpoint from tb a where id > (select max(id) from tb b where a.accountId  =b.accountId  and b.point <0 )

[cb1156]

引用 1 楼 zjcxc 的回复:

select accountId  , sum(point) as sumpoint from tb a where id > (select max(id) from tb b where a.accountId  =b.accountId  and b.point <0 )

这个只能得到第一个用户的啊。。

[二月十六]

SELECT  SUM(point) ,
        a.accountId
FROM    #T a
        JOIN ( SELECT   MAX(id) AS id ,
                        accountId
               FROM     #T
               WHERE    point < 0
               GROUP BY accountId
             ) t ON t.accountId = a.accountId
                    AND a.id > t.id
GROUP BY a.accountId

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
 CREATE TABLE #T(id INT,accountId INT ,[date] DATETIME ,point INT)
Insert #T
select 1,1,'2017-05-29 16:27:38',1 union all
select 2,1,'2017-05-30 16:27:48',-1 union all
select 3,1,'2017-05-31 16:28:06',1 union all
select 4,2,'2017-05-29 16:33:01',1 union all
select 5,2,'2017-05-30 16:33:22',-1
Go
--测试数据结束
SELECT  a.accountId ,
        SUM(point) AS sumPoint
FROM    #T a
        LEFT JOIN ( SELECT  MAX(id) AS id ,
                            accountId
                    FROM    #T
                    WHERE   point < 0
                    GROUP BY accountId
                  ) t ON t.accountId = a.accountId
                         AND a.id > t.id
GROUP BY a.accountId

[cb1156]

引用 4 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
 CREATE TABLE #T(id INT,accountId INT ,[date] DATETIME ,point INT)
Insert #T
select 1,1,'2017-05-29 16:27:38',1 union all
select 2,1,'2017-05-30 16:27:48',-1 union all
select 3,1,'2017-05-31 16:28:06',1 union all
select 4,2,'2017-05-29 16:33:01',1 union all
select 5,2,'2017-05-30 16:33:22',-1
Go
--测试数据结束
SELECT  a.accountId ,
        SUM(point) AS sumPoint
FROM    #T a
        LEFT JOIN ( SELECT  MAX(id) AS id ,
                            accountId
                    FROM    #T
                    WHERE   point < 0
                    GROUP BY accountId
                  ) t ON t.accountId = a.accountId
                         AND a.id > t.id
GROUP BY a.accountId

虽然有点问题。。但是改了一点点就好了。 感谢