表table
id date money
1 2011-06-02 11
2 2011-06-03 8
3 2011-06-04 7
4 2011-06-04 8
5 2011-06-05 10
6 2011-06-07 6
7 2011-06-08 6
8 2011-06-09 16
求sql 能查询出最近一周内 每一天的money总数结果
id date money
1 2011-06-03 8
2 2011-06-04 15
3 2011-06-05 10
4 2011-06-06 0
5 2011-06-07 6
6 2011-06-08 6
7 2011-06-09 16
id date money
1 2011-06-02 11
2 2011-06-03 8
3 2011-06-04 7
4 2011-06-04 8
5 2011-06-05 10
6 2011-06-07 6
7 2011-06-08 6
8 2011-06-09 16
求sql 能查询出最近一周内 每一天的money总数结果
id date money
1 2011-06-03 8
2 2011-06-04 15
3 2011-06-05 10
4 2011-06-06 0
5 2011-06-07 6
6 2011-06-08 6
7 2011-06-09 16
from tab
where date>= convert(varchar(10),dateadd(day,-7,getdate()),120)
group by date
select 1,'2011-06-02',11
union all select 2,'2011-06-03',8
union all select 3,'2011-06-04',7
union all select 4,'2011-06-04',8
union all select 5,'2011-06-05',10
union all select 6,'2011-06-07',6
union all select 7,'2011-06-08',6
union all select 8,'2011-06-09',16;with a as
( select date=convert(varchar(10),dateadd(day,-6,getdate()),120)
union all select date=convert(varchar(10),dateadd(day,-5,getdate()),120)
union all select date=convert(varchar(10),dateadd(day,-4,getdate()),120)
union all select date=convert(varchar(10),dateadd(day,-3,getdate()),120)
union all select date=convert(varchar(10),dateadd(day,-2,getdate()),120)
union all select date=convert(varchar(10),dateadd(day,-1,getdate()),120)
union all select date=convert(varchar(10),getdate(),120)
)
select id=ROW_NUMBER()OVER(ORDER BY t1.date),t1.date,[money]=sum(isnull(t2.[money],0))
from a t1 left join ta t2 on t1.date=t2.date
group by t1.date/*
1 2011-06-03 8
2 2011-06-04 15
3 2011-06-05 10
4 2011-06-06 0
5 2011-06-07 6
6 2011-06-08 6
7 2011-06-09 16
*/
from tab
where date>= convert(varchar(10),dateadd(day,-7,getdate()),120)
group by date
from master..spt_values left join tb
on type='p'
and date>= convert(varchar(10),dateadd(day,-7,getdate()),120)
and tb.date=datediff(dd,-number,getdate())
group by datediff(dd,-number,getdate())
from tab
where date>= convert(varchar(10),dateadd(day,-7,getdate()),120)
group by date
insert into #temp
select 1,'2011-06-02',11
union all select 2,'2011-06-03',8
union all select 3,'2011-06-04',7
union all select 4,'2011-06-04',8
union all select 5,'2011-06-05',10
union all select 6,'2011-06-07',6
union all select 7,'2011-06-08',6
union all select 8,'2011-06-09',16
--SQL:
;WITH cte AS
(
SELECT TOP(7)
[date]=DATEADD(DAY, -number, CONVERT(CHAR(10), GETDATE(), 120))
from master.dbo.spt_values
WHERE type = 'p'
)
SELECT
[date]=CONVERT(CHAR(10), a.[date], 120),
[money]=ISNULL(SUM(b.[money]), 0)
FROM cte a
LEFT JOIN #temp b
ON a.[date] = b.[date]
GROUP BY a.[date]
/*
date money
2011-06-03 8
2011-06-04 15
2011-06-05 10
2011-06-06 0
2011-06-07 6
2011-06-08 6
2011-06-09 16
*/
id=row_number()over(order by getdate()),date,sum(money) as money
from
tb
where
date>= convert(varchar(10),dateadd(day,-7,getdate()),120)
group by
date