连续编号分组合计问题

Question

有一个表如下:
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
请问如何写这个存储过程?

Answer 1

上面的结果应该是:
比如 2008-1-1到2008-1-3,得到的结果为: 
ID_Range  Totoal_Price 
1-3        114 
6-8        42 

Answer 2

--测试数据
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 id=IDENTITY(int),col1,col2 INTO #1 FROM tb a
WHERE NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2-1)
SELECT id=IDENTITY(int),col2 INTO #2 FROM tb a
WHERE NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
SELECT a.col1,start_col2=a.col2,end_col2=b.col2
FROM #1 a,#2 b
WHERE a.id=b.id
DROP TABLE #1,#2
/*--结果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          2           3
a          6           8
b          3           3
b          5           7
--*/

Answer 3

if object_id('tempdb..#T') is not null
   drop table #Tcreate 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 
goselect ID=ltrim(ID)+'-'+
         case when exists(select 1 from #T 
                     where SaleDate between '2008-01-01' and '2008-01-03' 
                               and ID=a.ID+1) then
               ltrim((select min(ID) from #T b
                          where SaleDate between '2008-01-01' and '2008-01-03' and ID>a.ID
                               and not exists(select 1 from #T 
                                               where SaleDate between '2008-01-01' and '2008-01-03' 
                                                  and ID=b.ID+1)
                       )) else '' end  ,       Total_Price=(select sum(Price) from #T b
                         where SaleDate between '2008-01-01' and '2008-01-03'
                             and ID>=a.ID 
                    )from #T a
where SaleDate between '2008-01-01' and '2008-01-03' 
        and not exists
            (select 1 from #T where ID=a.ID-1 and SaleDate between '2008-01-01' and '2008-01-03')go
drop table #T/*
ID                        Total_Price 
------------------------- ----------- 
1-3                       156
6-8                       42（所影响的行数为 2 行）
*/

Answer 4

if object_id('tempdb..#T') is not null
   drop table #Tcreate 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 
gocreate 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>=a.ID 
                    )   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                       156
6-8                       42（所影响的行数为 2 行）ID                        Total_Price 
------------------------- ----------- 
1-3                       124
6                         10（所影响的行数为 2 行）
*/

Answer 5

不好意思..
应该这样才对..if object_id('tempdb..#T') is not null
   drop table #Tcreate 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 
gocreate 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 行）
*/

Answer 6

set nocount on
CREATE TABLE tb(ID int,    saleDate smalldatetime,  Price int)
insert into tb values (1,'2008-1-1', 40)
insert into tb values (2,'2008-1-1', 50)
insert into tb values (3,'2008-1-2', 24)
insert into tb values (6,'2008-1-2', 10)
insert into tb values (7,'2008-1-3', 12)
insert into tb values (8,'2008-1-3', 20)
GOselect range = case when fmid=toid then cast(fmid as varchar) else cast(fmid as varchar)+'~'+cast(toid as varchar) end
    ,total_Price = sum(price)
from (
    select fmid=id , toid = (
        select min(id) 
        from tb a0 
        where a0.saleDate=a.saleDate 
        and a0.id>=a.id 
        and not exists (select 1 from tb b0 
            where b0.saleDate=a0.saleDate
            and b0.id=a0.id+1))
    from tb a where
    not exists (select 1 from tb b
        where b.saleDate=a.saleDate and b.id=a.id-1)
) as a
join tb b on b.id between a.fmid and a.toid
group by case when fmid=toid then cast(fmid as varchar) else cast(fmid as varchar)+'~'+cast(toid as varchar) end
go
drop table tb
go
-- range total_Price
-- 1~2 90
-- 3 24
-- 6 10
-- 7~8 32
-- 

Answer 7

改造一下。
set nocount on
CREATE TABLE tb(ID int, saleDate smalldatetime, Price int)
insert into tb values (1,'2008-1-1', 40)
insert into tb values (2,'2008-1-1', 50)
insert into tb values (3,'2008-1-2', 24)
insert into tb values (6,'2008-1-2', 10)
insert into tb values (7,'2008-1-3', 12)
insert into tb values (8,'2008-1-3', 20)
GO-- 定义查询区间参数
declare @bgn smalldatetime, @end smalldatetime
set @bgn = '2008-1-1'
set @end = '2008-1-3'-- 数据cache表
declare @tb table (id int, price int)
insert into @tb select id, price from tb where saledate between @bgn and @endselect range = case when fmid=toid then cast(fmid as varchar) else cast(fmid as varchar)+'~'+cast(toid as varchar) end
    ,total_Price = sum(price)
from (
    select fmid=id , toid = (
        select min(id) 
        from @tb a0 
        where a0.id>=a.id 
        and not exists (select 1 from @tb b0 
            where b0.id=a0.id+1))
    from @tb a where
    not exists (select 1 from @tb b
        where b.id=a.id-1)
) as a
join @tb b on b.id between a.fmid and a.toid
group by case when fmid=toid then cast(fmid as varchar) else cast(fmid as varchar)+'~'+cast(toid as varchar) end
go
drop table tb
go
-- range total_Price
-- 1~3 114
-- 6~8 42
-- 

Answer 8

liangCK 是对的.还有一个小问题,如何知道连续数据的值呢?
比如得到
ID       couts              Total_Price 
------------------------- ----------- 
1-3        3               114
6-8        4               42

Answer 9

set nocount on
CREATE TABLE tb(ID int, saleDate smalldatetime, Price int)
insert into tb values (1,'2008-1-1', 40)
insert into tb values (2,'2008-1-1', 50)
insert into tb values (3,'2008-1-2', 24)
insert into tb values (6,'2008-1-2', 10)
insert into tb values (7,'2008-1-3', 12)
insert into tb values (8,'2008-1-3', 20)
GO-- 定义查询区间参数
declare @bgn smalldatetime, @end smalldatetime
set @bgn = '2008-1-1'
set @end = '2008-1-3'-- 数据cache表
declare @tb table (id int, price int)
insert into @tb select id, price from tb where saledate between @bgn and @endselect range = case when fmid=toid then cast(fmid as varchar) else cast(fmid as varchar)+'~'+cast(toid as varchar) end
    ,couts = count(b.id)
    ,total_Price = sum(price)
from (
    select fmid=id , toid = (
        select min(id) 
        from @tb a0 
        where a0.id>=a.id 
        and not exists (select 1 from @tb b0 
            where b0.id=a0.id+1))
    from @tb a where
    not exists (select 1 from @tb b
        where b.id=a.id-1)
) as a
join @tb b on b.id between a.fmid and a.toid
group by case when fmid=toid then cast(fmid as varchar) else cast(fmid as varchar)+'~'+cast(toid as varchar) end
go
drop table tb
go
-- range couts total_Price
-- 1~3 3 114
-- 6~8 3 42
-- 

Answer 10

if object_id('tempdb..#T') is not null
   drop table #Tcreate 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 
gocreate 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  ,
         counts=(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))+1 -ID,
         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_testID                        counts      Total_Price 
------------------------- ----------- ----------- 
1-3                       3           114
6-8                       3           42（所影响的行数为 2 行）ID                        counts      Total_Price 
------------------------- ----------- ----------- 
1-3                       3           114
6                         1           10（所影响的行数为 2 行）