SQL分组汇总问题

[newqq]

CREATE TABLE #test(A VARCHAR(100),b FLOAT,c DATE)

INSERT INTO #test ( A, B ,c)
SELECT 'A01',10,'2017-10-06' UNION ALL
SELECT 'A01',50,'2017-10-07' UNION ALL
SELECT 'A01',30,'2017-10-16' UNION ALL
SELECT 'B01',10,'2017-10-06'
SELECT * FROM #test

想要的结果：
A01 60 2017-10-06
A01 30 2017-10-16
B01 10 2017-10-06

说明：按照A分组，取C的最小日期+3天，也就是2017-10-06至10月2017-10-09之内的记录汇总

[newqq]

最小日期加3天

[听雨停了]

引用 2楼我是你的主体 的回复:

最小日期加3天

如果还有一条A01的10-17的记录，是不是汇总后A01就有三条记录了

[newqq]

CREATE TABLE #test(A VARCHAR(100),b FLOAT,c DATE) INSERT INTO #test ( A, B ,c) SELECT 'A01',10,'2017-10-06' UNION ALL SELECT 'A01',50,'2017-10-07' UNION ALL SELECT 'A01',30,'2017-10-16' UNION ALL SELECT 'A01',5,'2017-10-17' UNION ALL SELECT 'A01',5,'2017-10-20' UNION ALL SELECT 'B01',10,'2017-10-06' UNION ALL SELECT 'B01',10,'2017-10-07' UNION ALL SELECT 'B01',10,'2017-10-20' --想要的结果： A01 60 2017-10-06 A01 35 2017-10-16 A01 5 2017-10-20 B01 10 2017-10-06 B01 10 2017-10-07 B01 20 2017-10-06 B01 10 2017-10-20

[newqq]

修正： CREATE TABLE #test(A VARCHAR(100),b FLOAT,c DATE) INSERT INTO #test ( A, B ,c) SELECT 'A01',10,'2017-10-06' UNION ALL SELECT 'A01',50,'2017-10-07' UNION ALL SELECT 'A01',30,'2017-10-16' UNION ALL SELECT 'A01',5,'2017-10-17' UNION ALL SELECT 'A01',5,'2017-10-20' UNION ALL SELECT 'B01',10,'2017-10-06' UNION ALL SELECT 'B01',10,'2017-10-07' UNION ALL SELECT 'B01',10,'2017-10-20' --结果： A01 60 2017-10-06 A01 35 2017-10-16 A01 5 2017-10-20 B01 20 2017-10-06 B01 10 2017-10-20

[newqq]

引用 3 楼 qq_37170555 的回复:

引用 2楼我是你的主体 的回复:

最小日期加3天

如果还有一条A01的10-17的记录，是不是汇总后A01就有三条记录了

和10-16的汇总

[砸死牛顿的苹果]

2017-10-6 2017-10-8 2017-10-9 2017-10-10 2017-10-11 合并结果是不是 2017-10-6 2017-10-10

[卖水果的net]

三天的数据汇总到一行？ 建议楼主多给出一些测试数据，和预期结果。

[二月十六]

if not object_id(N'Tempdb..#test') is null
	drop table #test
GO
CREATE TABLE #test(A VARCHAR(100),b FLOAT,c DATE) 
INSERT INTO #test ( A, B ,c) 
SELECT 'A01',10,'2017-10-06' UNION ALL 
SELECT 'A01',50,'2017-10-07' UNION ALL 
SELECT 'A01',30,'2017-10-16' UNION ALL 
SELECT 'A01',5,'2017-10-17' UNION ALL 
SELECT 'A01',5,'2017-10-20' UNION ALL 
SELECT 'B01',10,'2017-10-06' UNION ALL   
SELECT 'B01',10,'2017-10-07' UNION ALL  
SELECT 'B01',10,'2017-10-20'
if not object_id(N'Tempdb..#temptest') is null
	drop table #temptest
GO
CREATE TABLE #temptest(A VARCHAR(100),B FLOAT,C DATE) 
declare @A VARCHAR(100),@b FLOAT,@c DATE,@tb FLOAT
SET @tb=0
declare auth_cur cursor for
select A,B, C
from #test ORDER BY c
open auth_cur
fetch next from auth_cur into @A,@b,@c
while (@@fetch_status=0)
  begin
    IF(NOT EXISTS(SELECT * FROM #temptest WHERE A=@A AND DATEDIFF(DAY,c,@c)<=3 ))
	BEGIN
	INSERT INTO #temptest VALUES(@A,@b,@c)
	END
	ELSE
	BEGIN
	UPDATE #temptest SET b=b+@b WHERE A=@A AND DATEDIFF(DAY,c,@c)<=3
	END
    fetch next from auth_cur into @A,@b,@c
  end
close auth_cur
deallocate auth_cur
SELECT * FROM #temptest ORDER BY A,c

[砸死牛顿的苹果]

按A 和C字段 分组分段递归效率比递归应该高一点,为了验证密集日期分段准确性 我增加几行数据做测试

if object_id('tempdb.dbo.#test') is not null drop table #test
CREATE TABLE #test(A VARCHAR(100),b FLOAT,c DATE) 

INSERT INTO #test ( A, B ,c) 

SELECT 'A01',10,'2017-10-06' UNION ALL 
SELECT 'A01',50,'2017-10-07' UNION ALL 
SELECT 'A01',30,'2017-10-16' UNION ALL 
SELECT 'A01',5,'2017-10-17' UNION ALL 
SELECT 'A01',30,'2017-10-18' UNION ALL 
SELECT 'A01',5,'2017-10-20' UNION ALL 
SELECT 'A01',5,'2017-10-21' UNION ALL
SELECT 'A01',5,'2017-10-22' UNION ALL
SELECT 'B01',10,'2017-10-06' UNION ALL   
SELECT 'B01',10,'2017-10-07' UNION ALL  
SELECT 'B01',10,'2017-10-20'  
select * from #test
;with t as
	(
	select a,b,c from #test a where not exists(select 1 from #test b where a.a=b.a and (a.c > b.c and a.c<=dateadd(d,3,b.c)) )
	union all 
	select a.a,a.b,min(a.c)over(partition by t.a,t.c) from #test a,t where a.A =t.a and a.c > dateadd(d,3,t.c) and a.c <=dateadd(d,6,t.c)
	)
,t1 as(select distinct a,b,c from t)
select a,b=(select sum(b.b) from #test b where b.A =a.A and b.c between a.c and dateadd(d,3,a.c)),c from t1 a order by a,c

A	b	c
A01	10	2017-10-06
A01	50	2017-10-07
A01	30	2017-10-16
A01	5	2017-10-17
A01	30	2017-10-18
A01	5	2017-10-20
A01	5	2017-10-21
A01	5	2017-10-22
B01	10	2017-10-06
B01	10	2017-10-07
B01	10	2017-10-20

a	b	c
A01	60	2017-10-06
A01	65	2017-10-16
A01	15	2017-10-20
B01	20	2017-10-06
B01	10	2017-10-20

[砸死牛顿的苹果]

说错 应该比游标快一点

[nettman]

感谢楼主分享