sql server存储过程实例:统计一段时间内各连续ID的价格合计
来源:互联网 发布:ubuntu 移除ppa 编辑:程序博客网 时间:2024/06/11 10:43
有一个表如下:
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的价格合计
- SQL SERVER 统计一段时间内每天的数据
- Sql Server 给表添加合计并统计金额的存储过程!(源代码分享)
- 统计一段时间内的sql_id
- sql 统计一段时间内的工作日(除去星期六,星期天)
- sql server 的存储过程实例
- 统计一段时间内新建的session数
- sparkstreaming统计一段时间内的热搜词
- sql server存储过程实例
- SQL SERVER 存储过程实例
- SQL Server存储过程实例
- SQL Server存储过程实例
- sql server存储过程实例
- sql server 使用ROLLUP完成合计的统计
- 一个用于获取空ID的SQL Server存储过程
- 统计星期次数的SQL Server存储过程
- 统计星期次数的SQL Server存储过程
- sql 一段存储过程
- 《设计模式解析》 第17章 Decorate模式 复习题
- access函数使用
- sas做广义估计方程
- OpenCV图像类型转化
- html padding 和 margin
- sql server存储过程实例:统计一段时间内各连续ID的价格合计
- How to Perfectly Uninstall Ad-Aware Free Internet Security
- js location
- 安装openSUSE之后启动XP
- 计算出生日期那天是星期几
- 耿后旺2012年4月在公司的培训总结
- java web中jsp与servlet之间的跳转和传递参数
- stl 容器
- MFC COMBO BOX