按日期范围查询ID断号情况,并进行聚合求和

问:

有一个表如下:
ID     saleDate   Price
1      2008-1-1   40
2      2008-1-1   50
3      2008-1-2   24
6      2008-1-2   10
7      2008-1-3   12
8      2008-1-3   20
....
要写一个存储过程,统计一段时间内各连续ID的价格合计,得到以下结果:
比如 2008-1-1到2008-1-3,得到的结果为:
ID_Range  Totoal_Price
1-3        114
6-7        42
如果统计的是2008-1-1到2008-1-2,得到的结果是
ID_Range  Totoal_Price
1-3        114
6          10
请问如何写这个存储过程? 

 

答:

 

if object_id('tempdb..#T') is not null
   drop table #T

create table #T(ID int,SaleDate datetime,Price int)
insert into #T select 1   ,   '2008-1-1' ,  40 
insert into #T select 2   ,   '2008-1-1' ,  50 
insert into #T select 3   ,   '2008-1-2' ,  24 
insert into #T select 6   ,   '2008-1-2' ,  10 
insert into #T select 7   ,   '2008-1-3' ,  12 
insert into #T select 8   ,   '2008-1-3' ,  20 
go

create proc p_test
(
   @begin_date datetime,
   @end_date datetime
)
as
   select ID=ltrim(ID)+
         case when exists(select 1 from #T 
                     where SaleDate between @begin_date and @end_date 
                               and ID=a.ID+1) then '-'+
               ltrim((select min(ID) from #T b
                          where SaleDate between @begin_date and @end_date and ID>=a.ID
                               and not exists(select 1 from #T 
                                               where SaleDate between @begin_date and @end_date 
                                                  and ID=b.ID+1)
                       )) else '' end  ,

         Total_Price=(select sum(Price) from #T b
                         where SaleDate between @begin_date and @end_date
                             and ID between a.ID and 
                                    (select min(ID) from #T b
                                      where SaleDate between @begin_date and @end_date and ID>=a.ID
                                              and not exists(select 1 from #T 
                                                   where SaleDate between @begin_date and @end_date 
                                                         and ID=b.ID+1)
                                      )
                    )

   from #T a
   where SaleDate between @begin_date and @end_date
        and not exists
            (select 1 from #T where ID=a.ID-1 and SaleDate between @begin_date and @end_date)


go
exec p_test '2008-01-01','2008-01-03'
exec p_test '2008-01-01','2008-01-02'
go
drop table #T
drop proc p_test

/**//*
ID                        Total_Price 
------------------------- ----------- 
1-3                       114
6-8                       42

（所影响的行数为 2 行）

ID                        Total_Price 
------------------------- ----------- 
1-3                       114
6                         10

（所影响的行数为 2 行）
*/