--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',3
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO--已用编号分布查询
SELECT col1,start_col2=col2,
end_col2=(
SELECT MIN(col2) FROM tb aa
WHERE col1=a.col1 AND col2>=a.col2
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2+1))
FROM tb a
WHERE NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 and col2=a.col2-1)
/*--结果
col1 start_col2 end_col2
-------------- -------------- -----------
a 2 3
a 6 8
b 3 3
b 5 7
--*/
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',3
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO--已用编号分布查询
SELECT col1,start_col2=col2,
end_col2=(
SELECT MIN(col2) FROM tb aa
WHERE col1=a.col1 AND col2>=a.col2
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2+1))
FROM tb a
WHERE NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 and col2=a.col2-1)
/*--结果
col1 start_col2 end_col2
-------------- -------------- -----------
a 2 3
a 6 8
b 3 3
b 5 7
--*/
有一个表如下:
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
if object_id('tempdb.dbo.#') is not null drop table #
create table # (类型 varchar(1),id int)
insert into #
select 'A',1 union all
select 'A',2 union all
select 'A',3 union all
select 'A',4 union all
select 'A',6 union all
select 'A',8 union all
select 'A',10 union all
select 'A',11 union all
select 'A',12 union all
select 'B',21 union all
select 'C',21;with T as
(
select 类型,id as 起点,(select min(id) from # a where id>=t.id and not exists (select 1 from # where id=a.id+1)) as 终点 from # as t
)
select * from T as a where 起点=(select min(起点) from T where 终点=a.终点)/*
类型 起点 终点
---- ----------- -----------
A 1 4
A 6 6
A 8 8
A 10 12
B 21 21
C 21 21
*/