7,713
社区成员
发帖
与我相关
我的任务
分享
select top 7 *
from Table1 f inner join
(
select top 1 rdate
from (
SELECT a.rdate, sum(b.rainlevel) as sum7d
FROM Table1 a inner join Table1 b on
(a.rdate-b.rdate) between 0 and 6
group by a.rdate
order by 2 desc
)
) c
on f.rdate<=c.rdate
order by f.rdate desc
select top 7 *
from Table1 f ,
(
select top 1 rdate
from (
SELECT a.rdate, sum(b.rainlevel) as sum7d
FROM Table1 a inner join Table1 b on
(a.rdate-b.rdate) between 0 and 6
group by a.rdate
order by 2 desc
)
) c
where f.rdate<=c.rdate
order by f.rdate desc
f.rdate rainlevel c.rdate
2008-01-11 3 2008-01-11
2008-01-10 2 2008-01-11
2008-01-09 1 2008-01-11
2008-01-08 3 2008-01-11
2008-01-07 2 2008-01-11
2008-01-06 1 2008-01-11
2008-01-05 2 2008-01-11
select top 7 *
from Table1 f inner join
(
select top 1 rdate
from (
SELECT a.rdate, sum(b.rainlevel) as sum7d
FROM Table1 a, Table1 b
where (a.rdate-b.rdate) between 0 and 6
group by a.rdate
order by 2 desc
)
) c
on f.rdate<=c.rdate
order by f.rdate desc
rdate rainlevel
2008-01-01 1
2008-01-02 2
2008-01-03 3
2008-01-04 1
2008-01-05 2
2008-01-06 1
2008-01-07 2
2008-01-08 3
2008-01-09 1
2008-01-10 2
2008-01-11 3