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