现在需求变了，假设这个表的内容如下
TIME;SCORE;USER;ACTION1;ACTION2
08:00:00;50;zhao;123;abc
08:00:00;60;zhao;12345;bce
08:00:01;60;zhao;12345;bce
08:00:00;50;null;NULL;123;
08:00:50;20;qian;NULL;NULL
08:01:00;50;sun;xxx;yyy
08:01:00;50;sun;NULL;yyy
08:01:01;50;sun;NULL;yyy
08:02:00;10;li;YI;111
08:02:01;80;li;ER;NULL
08:02:01;50;li;SAN;222
新的需求是把时间相*近*的（2秒内），连续的user的score相加，并且返回首行的Action1和Action2值，得出以下结果
TIME;SCORE;USER;ACTION1;ACTION2
08:00:00;170;zhao;123;abc
08:00:50;20;qian;NULL;NULL
08:01:00;150;sun;xxx;yyy
08:02:00;10;li;YI;111
08:02:01;130;li;ER;NULL
下面的语句可以返回相同时间的，新的需求是把时间相*近*的。
SELECT TIME,TOTALSCORE SCORE,[USER],ACTION1,ACTION2 FROM(
SELECT *,Sum(Score)OVER(PARTITION BY [User],Time)TOTALSCORE
,ROW_NUMBER()OVER(PARTITION BY [User],Time ORDER BY GETDATE())RN
From Table1
)T
WHERE RN=1
------解决思路----------------------
真心建议你用存储过程来搞。。。按照你目前的需求，最好一条一条来处理，处理每一条数据都要基于之前的结果。比如，用户zhao在08:00:00, 08:01:30, 08:03:00各有一条得分记录，应该是08:00:00和08:01:30的合并，而08:03:00的就不应该再继续向前合并了。这种处理方式，不符合SQL语句集合操作的特点。代码供参考，大致思路是把需要向前合并的记录的TIME、ACTION1、ACTION2字段修改为2秒内第一条出现的记录的值，最后按照USER和TIME求聚合：
CREATE TABLE Table1
(
TIME time
, SCORE int
, [USER] varchar(10)
, ACTION1 varchar(10)
, ACTION2 varchar(10)
)
INSERT INTO Table1 VALUES ( '08:00:00', 50, 'zhao', '123', 'abc' )
INSERT INTO Table1 VALUES ( '08:00:00', 60, 'zhao', '12345', 'bce' )
INSERT INTO Table1 VALUES ( '08:00:01', 60, 'zhao', '12345', 'bce' )
INSERT INTO Table1 VALUES ( '08:00:00', 50, 'null', NULL, '123' )
INSERT INTO Table1 VALUES ( '08:00:50', 20, 'qian', NULL, NULL )
INSERT INTO Table1 VALUES ( '08:01:00', 50, 'sun', 'xxx', 'yyy' )
INSERT INTO Table1 VALUES ( '08:01:00', 50, 'sun', NULL, 'yyy' )
INSERT INTO Table1 VALUES ( '08:01:01', 50, 'sun', NULL, 'yyy' )
INSERT INTO Table1 VALUES ( '08:02:00', 10, 'li', 'YI', '111' )
INSERT INTO Table1 VALUES ( '08:02:01', 80, 'li', 'ER', NULL )
INSERT INTO Table1 VALUES ( '08:02:01', 50, 'li', 'SAN', '222' )
SELECT
ROW_NUMBER() OVER (ORDER BY GETDATE()) AS RN
, *
INTO #Table2
FROM Table1
DECLARE cur CURSOR
FOR ( SELECT RN, [USER], TIME FROM #Table2 )
FOR UPDATE
DECLARE @RN int
, @USER varchar(10)
, @TIME time
, @FirstRN int
OPEN cur
FETCH NEXT FROM cur INTO @RN, @USER, @TIME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@FirstRN = MIN(RN)
FROM #Table2
WHERE RN <= @RN
AND ( [USER] = @USER OR [USER] IS NULL )
AND TIME <= @TIME
AND TIME >= DATEADD(SS, -2, @TIME)
UPDATE a
SET TIME = b.TIME
, ACTION1 = b.ACTION1
, ACTION2 = b.ACTION2
FROM #Table2 a
JOIN (
SELECT TIME, ACTION1, ACTION2
FROM #Table2
WHERE RN = @FirstRN
) b ON 1 = 1
WHERE CURRENT OF cur
FETCH NEXT FROM cur INTO @RN, @USER, @TIME
END
CLOSE cur
DEALLOCATE cur
SELECT
CONVERT(time(0), TIME) AS TIME
, SUM(SCORE) AS SCORE
, [USER]
, MAX(ACTION1) AS ACTION1
, MAX(ACTION2) AS ACTION2
FROM #Table2
GROUP BY [USER], TIME
HAVING [USER] IS NOT NULL
ORDER BY TIME
------解决思路----------------------
你的结果同样是错的，zhao的每个分数都在1分钟以上，同样不应该合在一起
不过，我之前确实没考虑全，已修改，不过建议忽略NULL，因为按照你的意思，NULL应该合计在一起，也就是两NULL是一样的，那样，在你的需求下，语句会麻烦些
;WITH Start AS(
SELECT[Time],SCORE,[USER],ACTION1,ACTION2
,DENSE_RANK()OVER(PARTITION BY [USER]ORDER BY[Time])RN
,ROW_NUMBER()OVER(PARTITION BY [USER],[Time]ORDER BY GETDATE())Ty
FROM Table1 T1
WHERE NOT EXISTS
(SELECT 1 FROM Table1 T2
WHERE(T1.[USER]=T2.[USER]OR(T1.[USER]IS NULL AND T2.[USER]IS NULL))
AND T1.[Time]<=DATEADD(SECOND,2,T2.[Time])AND T1.[Time]>T2.[Time])
)
,[end]AS(
SELECT[Time],[USER]
,DENSE_RANK()OVER(PARTITION BY [USER]ORDER BY[Time])RN
,ROW_NUMBER()OVER(PARTITION BY [USER],[Time]ORDER BY GETDATE())Ty
FROM Table1 T1
WHERE NOT EXISTS
(SELECT 1 FROM Table1 T2
WHERE(T1.[USER]=T2.[USER]OR(T1.[USER]IS NULL AND T2.[USER]IS NULL))
AND T1.[Time]>=DATEADD(SECOND,-2,T2.[Time])AND T1.[Time]<T2.[Time])
)
SELECT T1.[Time],SUM(T3.SCORE)TOTALSCORE,T1.[USER],T1.ACTION1,T1.ACTION2
FROM Start T1 JOIN[end]T2 ON(T1.[USER]=T2.[USER]OR(T1.[USER]IS NULL AND T2.[USER]IS NULL))
AND T1.Ty=1 AND T2.Ty=1 AND T1.RN=T2.RN
JOIN Table1 T3 ON(T1.[USER]=T3.[USER]OR(T1.[USER]IS NULL AND T3.[USER]IS NULL))
AND T1.[Time]<=T3.[Time]AND T3.[Time]<=T2.[Time]
GROUP BY T1.[Time],T1.[USER],T1.ACTION1,T1.ACTION2