还是希望求教一个sql写法
有一张表
num date money
A 2013-05-27 100
A 2013-05-24 200
A 2013-05-22 100
A 2013-05-20 1200
A 2013-05-19 300
A 2013-05-16 400
A 2013-05-12 200
A 2013-05-09 100
A 2013-05-06 500
A 2013-05-01 150
开始时间就以05-27为开始
那么5天一个时间段就是
05-27到05-23
05-22到05-18
05-17到05-13
05-12到05-08
05-07到05-03
05-02到04-28
那么当中金额合计最大的就是 05-22到05-18 金额合计是 1600
我就想求出这个1600
不知道这样写看的明白么。。。。
[解决办法]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (num nvarchar(2),date datetime,money int)
insert into [TB]
select 'A','2013-05-27',100 union all
select 'A','2013-05-24',200 union all
select 'A','2013-05-22',100 union all
select 'A','2013-05-20',1200 union all
select 'A','2013-05-19',300 union all
select 'A','2013-05-16',400 union all
select 'A','2013-05-12',200 union all
select 'A','2013-05-09',100 union all
select 'A','2013-05-06',500 union all
select 'A','2013-05-01',150
select * from [TB]
;WITH TT
AS(
SELECT DATEADD(dd,number,'2013-05-01') AS dd
FROM master..spt_values M
WHERE type ='P'),
T1 AS(
SELECT TT.dd,TB.date,TB.money
FROM TT
LEFT JOIN TB ON TT.dd = TB.date),
T2 AS(
SELECT TOP 100 PERCENT dd,date,money,convert(int,ROW_NUMBER() OVER(ORDER BY GETDATE())/6) AS nu
FROM T1
WHERE NOT EXISTS ( SELECT 1
FROM T1 A
WHERE NOT EXISTS ( SELECT 1
FROM T1 B
WHERE A.dd <= b.date )
AND T1.dd = a.dd )
ORDER BY dd DESC)
SELECT TOP 1 SUM(money)
FROM T2
GROUP BY nu
ORDER BY SUM(money) DESC
--1600
select max(total) from (select SUM(Money) as total from t1 group by DATEDIFF(day,'2013-05-27 00:00:00.000',[date])/5)t