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 行)
*/
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