sql server存储过程实例：统计一段时间内各连续ID的价格合计

http://www.itwis.com/html/database/sqlserver/20080422/1335.html

问:
有一个表如下:
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 行）
*/

相关推荐：SQL Server自增ID值不连续记录的实现方法：http://database.ctocio.com.cn/196/11511696.shtml



转载于:https://www.cnblogs.com/dlz5257/archive/2011/04/14/tie2.html