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