IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb
(
Uid VARCHAR(8),
Td DATETIME,
Pay int,
quantity bigint
)
INSERT INTO #tb
VALUES('00001','2014-01-01 09:15:01',10,300),
('00001','2014-01-01 09:15:01',12,2500),
('00001','2014-01-01 09:15:01',12,500),
('00001','2014-01-01 09:15:01',12,800),
('00001','2014-01-01 09:15:02',10,700),
('00001','2014-01-01 09:15:02',10,3300),
('00001','2014-01-01 09:15:02',11,3500),
('00001','2014-01-01 09:15:02',11,1000),
('00001','2014-01-01 09:15:02',11,100),
('00001','2014-01-01 09:15:02',11,730),
('00001','2014-01-02 09:15:01',12,2000),
('00001','2014-01-02 09:15:01',13,300),
('00001','2014-01-02 09:15:01',13,530),
('00001','2014-01-02 09:15:02',12,630),
('00001','2014-01-02 09:15:03',12,3300),
('00001','2014-01-02 09:15:03',12,500),
('00001','2014-01-02 09:15:03',11,900),
('00001','2014-01-02 09:15:04',11,120),
('00001','2014-01-02 09:15:05',11,250),
('00002','2014-02-01 09:15:01',9,870),
('00002','2014-02-01 09:15:01',9,330),
('00002','2014-02-01 09:15:01',10,320),
('00002','2014-02-01 09:15:01',10,4000),
('00002','2014-02-01 09:15:02',10,2000),
('00002','2014-02-01 09:15:02',10,600),
('00002','2014-02-01 09:15:02',8,400),
('00002','2014-02-01 09:15:02',11,3000),
('00002','2014-02-01 09:15:02',11,800),
('00002','2014-02-01 09:15:02',11,700)
/***********要实现得到如下结果：***************************/
Uid 日期 Pay total
00001 2014-01-01 10 300
00001 2014-01-01 12 3800
00001 2014-01-01 10 4000
00001 2014-01-01 11 5330
00001 2014-01-02 12 2000
00001 2014-01-02 13 830
00001 2014-01-02 12 4430
00001 2014-01-02 11 1270
00002 2014-02-01 9 1200
00002 2014-02-01 10 6920
00002 2014-02-01 8 400
00002 2014-02-01 11 4500
相同的数值就是Pay这个字段，对每个Uid按不同日期，Pay字段下的相同的数值只有相邻的才需要进行汇总它们的quantity。如果不相邻，即使相同也不需要汇总。
------解决思路----------------------
已修改排序
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN,ROW_NUMBER()OVER(PARTITION BY Pay ORDER BY GETDATE())RN2,*FROM #tb
)
,CTE2 AS(
SELECT Pay,RN-RN2 R12,MIN(RN)ORD FROM CTE GROUP BY Pay,RN-RN2
)
SELECT A.Uid,CONVERT(VARCHAR(10),A.Td,120)Td,A.Pay,SUM(A.quantity)total
FROM CTE A
JOIN CTE2 B ON A.Pay=B.Pay AND B.R12=A.RN-A.RN2
GROUP BY A.RN-A.RN2,A.Uid,CONVERT(VARCHAR(10),A.Td,120),A.Pay,B.ORD
ORDER BY B.ORD