[求助]对一个连续重复数据的复杂求和

Question

重复求和

Answer 1

--#tb csid  sdate  bs  xl
--sql2005
;with cte as
(
    select *,
        rid = row_number()
    from #tb
),cta as
(
    select *,
        ret = rid - row_number() over (partition by csid,sdate,bs order by rid)
    from cte
)select
    csid,sdate,bs,count(1) as counts,sum(xl) as total
from cta
group by csid,sdate,bs

Answer 2

第一个rid = row_number()后面是不是漏了语句？

Answer 3

row_number() over() 
试试！

Answer 4

更正一下示例中第一条记录的销售时间错误，记录已经是排序后的了，前面的记录其销售时间应该最小。INSERT INTO #tb
VALUES
('00001','2014-01-01 09:16:09',0,100),
('00001','2014-01-01 09:16:10',1,150),
('00001','2014-01-01 09:16:11',1,1000),
('00001','2014-01-01 09:16:12',1,260),
('00001','2014-01-01 09:16:13',1,7100),
('00001','2014-01-01 09:17:14',0,336),
('00001','2014-01-01 09:17:15',0,40),
('00001','2014-01-02 11:20:22',1,100),
('00001','2014-01-02 11:20:52',1,200),
('00001','2014-01-02 11:20:52',1,180),
('00001','2014-01-02 11:25:01',1,500),
('00001','2014-01-02 11:33:14',0,6000),
('00001','2014-01-02 11:33:52',0,20000),
('00002','2014-01-03 10:15:01',1,100),
('00002','2014-01-03 10:16:10',0,150),
('00002','2014-01-03 10:16:11',0,1000),
('00002','2014-01-03 10:16:12',0,260),
('00002','2014-01-03 10:16:13',1,7100),
('00002','2014-01-04 13:25:01',0,500),
('00002','2014-01-04 13:33:14',0,6000),
('00002','2014-01-04 13:33:52',0,20000)

Answer 5

那你就直接
row_number() over (order by csid,sdate) 
执行查询看看。

Answer 6

奇怪，为啥回复的就少了几个字，over括号里的。

Answer 7

IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb
(
csid varchar(6),  ----用户ID
sdate datetime,   ----销售时间
bs bit,           ----标识
xl int            ----销售量
)INSERT INTO #tb
VALUES
('00001','2014-01-01 09:16:11',0,100),
('00001','2014-01-01 09:16:10',1,150),
('00001','2014-01-01 09:16:11',1,1000),
('00001','2014-01-01 09:16:12',1,260),
('00001','2014-01-01 09:16:13',1,7100),
('00001','2014-01-01 09:17:14',0,336),
('00001','2014-01-01 09:17:15',0,40),
('00001','2014-01-02 11:20:22',1,100),
('00001','2014-01-02 11:20:52',1,200),
('00001','2014-01-02 11:20:52',1,180),
('00001','2014-01-02 11:25:01',1,500),
('00001','2014-01-02 11:33:14',0,6000),
('00001','2014-01-02 11:33:52',0,20000),
('00002','2014-01-03 10:15:01',1,100),
('00002','2014-01-03 10:16:10',0,150),
('00002','2014-01-03 10:16:11',0,1000),
('00002','2014-01-03 10:16:12',0,260),
('00002','2014-01-03 10:16:13',1,7100),
('00002','2014-01-04 13:25:01',0,500),
('00002','2014-01-04 13:33:14',0,6000),
('00002','2014-01-04 13:33:52',0,20000); WITH CTE_S AS(
SELECT csid, t.sdate, bs, xl, S.sdate AS GroupDate  
FROM #tb t 
OUTER APPLY (SELECT TOP 1 sdate FROM #tb WHERE sdate <t.sdate AND bs<>t.bs ORDER BY sdate DESC) S 
)SELECT csid, CONVERT(varchar(10),sdate,120), bs, COUNT(*) AS [COUNT], SUM(xl) AS Total 
FROM CTE_S G
GROUP BY G.bs, G.GroupDate,csid, CONVERT(varchar(10),G.sdate,120) 
ORDER BY G.GroupDate, G.bs, G.csid, CONVERT(varchar(10),G.sdate,120)