该博客讨论了如何编写一个SQL存储过程，用于在指定日期范围内查找表中ID的连续断号情况，并计算这些连续ID的价格总和。举例来说，从2008-1-1到2008-1-3，结果应展示连续ID的价格合计，如1-3的总价为114。博主寻求解决方案并提供了示例数据。
问:
有一个表如下:
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 行）
*/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
