数据以 60天 分组汇总。比较特殊，网上还没有这种例子。求一个存储过程，在线等

Question

我要得到以下数据表60天内的汇总
bqid （主键）        商品            姓名                时间                         成本               金额
  1                 AA             A         2008-05-01 23:18:47            50                  70      
  2                 BB             A         2008-05-02 20:10:40            10                  10     
  3                 GG             C         2008-05-06 23:18:49            20                  90     
  4                 AA             AB        2008-05-02 21:00:00            60                  40     
  5                 AA             D         2008-05-07 23:00:00            10                  70     
  6                 AA             XX        2008-05-06 23:00:00            5                   40     
 以上为数据表结构，我要生成以下结果。比如今天是 2008-05-07 ，我要让它显示一个月之内的数据（就是30内的数据）。     日期             成本           金额 
2008-05-07         10              70 
2008-05-06         25 <--20+5      130 <--90+40  <---日期一样就SUM(成本) SUM（金额)
2008-05-05          0              0 <---4号没有数据
2008-05-04          0              0 
2008-05-03          0              0  
2008-05-02         70              50 
2008-05-01         50              70
2008-04-30          0              0 
2008-04-29          0              0
2008-04-28          0              0.....略
2008-04-08          0              0

Answer 1

     日期             成本           金额 
2008-05-07         10              70 
2008-05-06         25 <--20+5      130 <--90+40  <---日期一样就SUM(成本) SUM（金额)
2008-05-05          0              0 <---5号没有数据------刚刚这里笔误
2008-05-04          0              0 
2008-05-03          0              0  
2008-05-02         70              50 
2008-05-01         50              70
2008-04-30          0              0 
2008-04-29          0              0
2008-04-28          0              0.....略
2008-04-08          0              0

Answer 2

比如今天是 2008-05-07 ，我要让它显示一个月之内的数据（就是30内的数据）。
--------------
select * from tb
where 日期 between dateadd(mm,-1,getdate()) and getdate()

Answer 3

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (bqid int,商品 varchar(2),姓名 varchar(2),时间 datetime,成本 int,金额 int)
insert into #T
select 1,'AA','A','2008-05-01 23:18:47',50,70 union all
select 2,'BB','A','2008-05-02 20:10:40',10,10 union all
select 3,'GG','C','2008-05-06 23:18:49',20,90 union all
select 4,'AA','AB','2008-05-02 21:00:00',60,40 union all
select 5,'AA','D','2008-05-07 23:00:00',10,70 union all
select 6,'AA','XX','2008-05-06 23:00:00',5,40declare @date datetime
set @date = '2008-05-07';with date as
(
select @date as date
union all
select date-1 from date where date>@date-29
)
select a.date as 日期,sum(isnull(b.成本,0))成本,sum(isnull(b.金额,0))金额 from date a left join #T b on datediff(day,a.date,b.时间)=0 group by a.date
/*
日期                    成本        金额
----------------------- ----------- -----------
2008-04-08 00:00:00.000 0           0
2008-04-09 00:00:00.000 0           0
2008-04-10 00:00:00.000 0           0
2008-04-11 00:00:00.000 0           0
2008-04-12 00:00:00.000 0           0
2008-04-13 00:00:00.000 0           0
2008-04-14 00:00:00.000 0           0
2008-04-15 00:00:00.000 0           0
2008-04-16 00:00:00.000 0           0
2008-04-17 00:00:00.000 0           0
2008-04-18 00:00:00.000 0           0
2008-04-19 00:00:00.000 0           0
2008-04-20 00:00:00.000 0           0
2008-04-21 00:00:00.000 0           0
2008-04-22 00:00:00.000 0           0
2008-04-23 00:00:00.000 0           0
2008-04-24 00:00:00.000 0           0
2008-04-25 00:00:00.000 0           0
2008-04-26 00:00:00.000 0           0
2008-04-27 00:00:00.000 0           0
2008-04-28 00:00:00.000 0           0
2008-04-29 00:00:00.000 0           0
2008-04-30 00:00:00.000 0           0
2008-05-01 00:00:00.000 50          70
2008-05-02 00:00:00.000 70          50
2008-05-03 00:00:00.000 0           0
2008-05-04 00:00:00.000 0           0
2008-05-05 00:00:00.000 0           0
2008-05-06 00:00:00.000 25          130
2008-05-07 00:00:00.000 10          70
*/

Answer 4

TO： Limpire 服务器: 消息 156，级别 15，状态 1，行 4
在关键字 'with' 附近有语法错误。

Answer 5

小楼的代码只能在SQL2005下运行，SQL2000下得改改

Answer 6

晕,我只会简单的TSQL,不会改种,救命啊.

Answer 7

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (bqid int,商品 varchar(2),姓名 varchar(2),时间 datetime,成本 int,金额 int)
insert into #T
select 1,'AA','A','2008-05-01 23:18:47',50,70 union all
select 2,'BB','A','2008-05-02 20:10:40',10,10 union all
select 3,'GG','C','2008-05-06 23:18:49',20,90 union all
select 4,'AA','AB','2008-05-02 21:00:00',60,40 union all
select 5,'AA','D','2008-05-07 23:00:00',10,70 union all
select 6,'AA','XX','2008-05-06 23:00:00',5,40declare @date datetime,@offset int
set @date = '2008-05-07'
set @offset = datediff(day,0,@date);if object_id('tempdb.dbo.#') is not null drop table #
select top 30 date=identity(int,0,-1) into # from sysobjects
select convert(datetime,a.date+@offset) as 日期,sum(isnull(b.成本,0))成本,sum(isnull(b.金额,0))金额 from # a left join #T b on datediff(day,a.date,b.时间)=@offset group by convert(datetime,a.date+@offset)
/*
日期                    成本        金额
----------------------- ----------- -----------
2008-04-08 00:00:00.000 0           0
2008-04-09 00:00:00.000 0           0
2008-04-10 00:00:00.000 0           0
2008-04-11 00:00:00.000 0           0
2008-04-12 00:00:00.000 0           0
2008-04-13 00:00:00.000 0           0
2008-04-14 00:00:00.000 0           0
2008-04-15 00:00:00.000 0           0
2008-04-16 00:00:00.000 0           0
2008-04-17 00:00:00.000 0           0
2008-04-18 00:00:00.000 0           0
2008-04-19 00:00:00.000 0           0
2008-04-20 00:00:00.000 0           0
2008-04-21 00:00:00.000 0           0
2008-04-22 00:00:00.000 0           0
2008-04-23 00:00:00.000 0           0
2008-04-24 00:00:00.000 0           0
2008-04-25 00:00:00.000 0           0
2008-04-26 00:00:00.000 0           0
2008-04-27 00:00:00.000 0           0
2008-04-28 00:00:00.000 0           0
2008-04-29 00:00:00.000 0           0
2008-04-30 00:00:00.000 0           0
2008-05-01 00:00:00.000 50          70
2008-05-02 00:00:00.000 70          50
2008-05-03 00:00:00.000 0           0
2008-05-04 00:00:00.000 0           0
2008-05-05 00:00:00.000 0           0
2008-05-06 00:00:00.000 25          130
2008-05-07 00:00:00.000 10          70
*/