有一个表如下:
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 1146-8 42（所影响的行数为 2 行）ID Total_Price ------------------------- ----------- 1-3 1146 10（所影响的行数为 2 行）
*/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
