历史价格表的结构如下： 日期 商品编码 价格
20091101 033001 11.9
20091101 033002 30.5
20091101 033003 2.9
20091102 033001 11.9
20091102 033002 30
20091102 033003 2.9
20091103 033001 11.5
20091103 033002 30.5
20091103 033003 2.5
20091104 033001 11.5
20091104 033002 30.5
20091104 033003 2.5
20091105 033001 11.5
20091105 033002 30.5
20091105 033003 2.8
20091106 033001 11.5
20091106 033002 30
20091106 033003 2.9
20091107 033001 11
20091107 033002 30
20091107 033003 2
20091108 033001 10.8
20091108 033002 30
20091108 033003 2现要查出，每一种商品，它每一种价格的连续销售的持续天数的出现次数。如，
日期 商品编码 价格
20091101 033001 11.9
20091102 033001 11.9
20091103 033001 11.5
20091104 033001 11.5
20091105 033001 11.5
20091106 033001 11.5
20091107 033001 11
20091108 033001 10.8假设后面两天的价格是：
20091109 033001 11.9
20091110 033001 11.9那么，查询的结果应该是：
价格11.9连续销售2天的情况，出现了2次；
价格11.5连续销售4天的情况，出现了1次；
价格11连续销售1天的情况，出现了1次；
价格10.8连续销售1天的情况，出现了1次。历史价格表的记录在几百万级，商品有一万多种；假设该有的索引都有。请高手给出解决办法，要同时要兼顾查询语句的执行性能，谢谢！
20091101 033001 11.9
20091101 033002 30.5
20091101 033003 2.9
20091102 033001 11.9
20091102 033002 30
20091102 033003 2.9
20091103 033001 11.5
20091103 033002 30.5
20091103 033003 2.5
20091104 033001 11.5
20091104 033002 30.5
20091104 033003 2.5
20091105 033001 11.5
20091105 033002 30.5
20091105 033003 2.8
20091106 033001 11.5
20091106 033002 30
20091106 033003 2.9
20091107 033001 11
20091107 033002 30
20091107 033003 2
20091108 033001 10.8
20091108 033002 30
20091108 033003 2现要查出，每一种商品，它每一种价格的连续销售的持续天数的出现次数。如，
日期 商品编码 价格
20091101 033001 11.9
20091102 033001 11.9
20091103 033001 11.5
20091104 033001 11.5
20091105 033001 11.5
20091106 033001 11.5
20091107 033001 11
20091108 033001 10.8假设后面两天的价格是：
20091109 033001 11.9
20091110 033001 11.9那么，查询的结果应该是：
价格11.9连续销售2天的情况，出现了2次；
价格11.5连续销售4天的情况，出现了1次；
价格11连续销售1天的情况，出现了1次；
价格10.8连续销售1天的情况，出现了1次。历史价格表的记录在几百万级，商品有一万多种；假设该有的索引都有。请高手给出解决办法，要同时要兼顾查询语句的执行性能，谢谢！
declare cur cursor fast_forward for
select id,name from a;
open cur;
fetch next from cur into @id,@name;
while @@fetch_status=0
begin
--做你要做的事
fetch next from cur into @id,@name;
end
close cur;
deallocate cur;
----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-11-20 09:52:29
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([日期] datetime,[商品编码] varchar(6),[价格] numeric(3,1))
insert [tb]
select '20091101','033001',11.9 union all
select '20091101','033002',30.5 union all
select '20091101','033003',2.9 union all
select '20091102','033001',11.9 union all
select '20091102','033002',30 union all
select '20091102','033003',2.9 union all
select '20091103','033001',11.5 union all
select '20091103','033002',30.5 union all
select '20091103','033003',2.5 union all
select '20091104','033001',11.5 union all
select '20091104','033002',30.5 union all
select '20091104','033003',2.5 union all
select '20091105','033001',11.5 union all
select '20091105','033002',30.5 union all
select '20091105','033003',2.8 union all
select '20091106','033001',11.5 union all
select '20091106','033002',30 union all
select '20091106','033003',2.9 union all
select '20091107','033001',11 union all
select '20091107','033002',30 union all
select '20091107','033003',2 union all
select '20091108','033001',10.8 union all
select '20091108','033002',30 union all
select '20091108','033003',2
--------------开始查询--------------------------select *,id=row_number()over(partition by 价格 order by 日期,价格) from [tb]
----------------结果----------------------------
/* 日期 商品编码 价格 id
----------------------- ------ --------------------------------------- --------------------
2009-11-07 00:00:00.000 033003 2.0 1
2009-11-08 00:00:00.000 033003 2.0 2
2009-11-03 00:00:00.000 033003 2.5 1
2009-11-04 00:00:00.000 033003 2.5 2
2009-11-05 00:00:00.000 033003 2.8 1
2009-11-01 00:00:00.000 033003 2.9 1
2009-11-02 00:00:00.000 033003 2.9 2
2009-11-06 00:00:00.000 033003 2.9 3
2009-11-08 00:00:00.000 033001 10.8 1
2009-11-07 00:00:00.000 033001 11.0 1
2009-11-03 00:00:00.000 033001 11.5 1
2009-11-04 00:00:00.000 033001 11.5 2
2009-11-05 00:00:00.000 033001 11.5 3
2009-11-06 00:00:00.000 033001 11.5 4
2009-11-01 00:00:00.000 033001 11.9 1
2009-11-02 00:00:00.000 033001 11.9 2
2009-11-02 00:00:00.000 033002 30.0 1
2009-11-06 00:00:00.000 033002 30.0 2
2009-11-07 00:00:00.000 033002 30.0 3
2009-11-08 00:00:00.000 033002 30.0 4
2009-11-01 00:00:00.000 033002 30.5 1
2009-11-03 00:00:00.000 033002 30.5 2
2009-11-04 00:00:00.000 033002 30.5 3
2009-11-05 00:00:00.000 033002 30.5 4(24 行受影响)*/
insert into @tb select '20091101','033001',11.9
union all select '20091101','033002',30.5
union all select '20091101','033003',2.9
union all select '20091102','033001',11.9
union all select '20091102','033002',30
union all select '20091102','033003',2.9
union all select '20091103','033001',11.5
union all select '20091103','033002',30.5
union all select '20091103','033003',2.5
union all select '20091104','033001',11.5
union all select '20091104','033002',30.5
union all select '20091104','033003',2.5
union all select '20091105','033001',11.5
union all select '20091105','033002',30.5
union all select '20091105','033003',2.8
union all select '20091106','033001',11.5
union all select '20091106','033002',30
union all select '20091106','033003',2.9
union all select '20091107','033001',11
union all select '20091107','033002',30
union all select '20091107','033003',2
union all select '20091108','033001',10.8
union all select '20091108','033002',30
union all select '20091108','033003',2
union all select '20091109','033001',11.9
union all select '20091110','033001',11.9
;With china
as
(
select 日期,商品编码,价格,连续天数=(select count(1)+1 from @tb where 日期=a.日期-1 and 商品编码=
a.商品编码 and 价格=a.价格)
from @tb a
)
select 商品编码,价格,连续天数,出数次数=count(*) from china
group by 商品编码,价格,连续天数/*商品编码 价格 连续天数 出数次数
---------- --------------------------------------- ----------- -----------
033001 10.8 1 1
033001 11.0 1 1
033001 11.5 1 1
033001 11.5 2 3
033001 11.9 1 2
033001 11.9 2 2
033002 30.0 1 2
033002 30.0 2 2
033002 30.5 1 2
033002 30.5 2 2
033003 2.0 1 1
033003 2.0 2 1
033003 2.5 1 1
033003 2.5 2 1
033003 2.8 1 1
033003 2.9 1 2
033003 2.9 2 1
*/
chinajiabing 谢谢参与。033001 11.5 ，连续了4天，但似乎你得到的结果不是这个呀。连续4天，不能把它拆为3个连续2天的。
insert [tb]
select '20091101','033001',11.9 union all
select '20091101','033002',30.5 union all
select '20091101','033003',2.9 union all
select '20091102','033001',11.9 union all
select '20091102','033002',30 union all
select '20091102','033003',2.9 union all
select '20091103','033001',11.5 union all
select '20091103','033002',30.5 union all
select '20091103','033003',2.5 union all
select '20091104','033001',11.5 union all
select '20091104','033002',30.5 union all
select '20091104','033003',2.5 union all
select '20091105','033001',11.5 union all
select '20091105','033002',30.5 union all
select '20091105','033003',2.8 union all
select '20091106','033001',11.5 union all
select '20091106','033002',30 union all
select '20091106','033003',2.9 union all
select '20091107','033001',11 union all
select '20091107','033002',30 union all
select '20091107','033003',2 union all
select '20091108','033001',10.8 union all
select '20091108','033002',30 union all
select '20091108','033003',2
create table tmp(id varchar(6), price decimal(18,1) , [day] int)
godeclare @dt1 as varchar(8) ,@id1 varchar(6) , @price1 decimal(18,1);
declare @dt2 as varchar(8) ,@id2 varchar(6) , @price2 decimal(18,1);
declare @cnt as int
set @cnt = 0
set @dt2 = ''
set @id2 = ''
set @price2 = 0.0
declare cur cursor fast_forward for
select dt , id , price from tb order by id , dt , price;
open cur;
fetch next from cur into @dt1,@id1,@price1;
while @@fetch_status=0
begin
if datediff(day , @dt2 , @dt1) = 1 and @id1 = @id2 and @price1 = @price2
begin
set @dt2 = @dt1
set @cnt = @cnt + 1
end
else
begin
if @cnt > 0
begin
insert into tmp select @id2 , @price2 , @cnt+1
end
set @dt2 = @dt1
set @id2 = @id1
set @price2 = @price1
set @cnt = 0
end
fetch next from cur into @dt1,@id1,@price1;
end
close cur;
deallocate cur;select id , price , [day] , count(1) cnt from tmp group by id , price , [day]drop table tb,tmp/*
id price day cnt
------ -------------------- ----------- -----------
033001 11.5 4 1
033001 11.9 2 1
033002 30.0 3 1
033002 30.5 3 1
033003 2.5 2 1
033003 2.9 2 1（所影响的行数为 6 行）
*/
insert [tb]
select '20091101','033001',11.9 union all
select '20091101','033002',30.5 union all
select '20091101','033003',2.9 union all
select '20091102','033001',11.9 union all
select '20091102','033002',30 union all
select '20091102','033003',2.9 union all
select '20091103','033001',11.5 union all
select '20091103','033002',30.5 union all
select '20091103','033003',2.5 union all
select '20091104','033001',11.5 union all
select '20091104','033002',30.5 union all
select '20091104','033003',2.5 union all
select '20091105','033001',11.5 union all
select '20091105','033002',30.5 union all
select '20091105','033003',2.8 union all
select '20091106','033001',11.5 union all
select '20091106','033002',30 union all
select '20091106','033003',2.9 union all
select '20091107','033001',11 union all
select '20091107','033002',30 union all
select '20091107','033003',2 union all
select '20091108','033001',10.8 union all
select '20091108','033002',30 union all
select '20091108','033003',2 union all
select '20091109','033001',11.9 union all
select '20091110','033001',11.9 create table tmp(id varchar(6), price decimal(18,1) , [day] int)
godeclare @dt1 as varchar(8) ,@id1 varchar(6) , @price1 decimal(18,1);
declare @dt2 as varchar(8) ,@id2 varchar(6) , @price2 decimal(18,1);
declare @cnt as int
set @cnt = 0
set @dt2 = ''
set @id2 = ''
set @price2 = 0.0
declare cur cursor fast_forward for
select dt , id , price from tb order by id , dt , price;
open cur;
fetch next from cur into @dt1,@id1,@price1;
while @@fetch_status=0
begin
if datediff(day , @dt2 , @dt1) = 1 and @id1 = @id2 and @price1 = @price2
begin
set @dt2 = @dt1
set @cnt = @cnt + 1
end
else
begin
if @cnt > 0
begin
insert into tmp select @id2 , @price2 , @cnt+1
end
set @dt2 = @dt1
set @id2 = @id1
set @price2 = @price1
set @cnt = 0
end
fetch next from cur into @dt1,@id1,@price1;
end
close cur;
deallocate cur;select id , price , [day] , count(1) cnt from tmp group by id , price , [day]drop table tb,tmp/*
id price day cnt
------ -------------------- ----------- -----------
033001 11.5 4 1
033001 11.9 2 2
033002 30.0 3 1
033002 30.5 3 1
033003 2.5 2 1
033003 2.9 2 1（所影响的行数为 6 行）
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([日期] datetime,[商品编码] varchar(6),[价格] numeric(3,1))
insert [tb]
select '20091101','033001',11.9 union all
select '20091101','033002',30.5 union all
select '20091101','033003',2.9 union all
select '20091102','033001',11.9 union all
select '20091102','033002',30 union all
select '20091102','033003',2.9 union all
select '20091103','033001',11.5 union all
select '20091103','033002',30.5 union all
select '20091103','033003',2.5 union all
select '20091104','033001',11.5 union all
select '20091104','033002',30.5 union all
select '20091104','033003',2.5 union all
select '20091105','033001',11.5 union all
select '20091105','033002',30.5 union all
select '20091105','033003',2.8 union all
select '20091106','033001',11.5 union all
select '20091106','033002',30 union all
select '20091106','033003',2.9 union all
select '20091107','033001',11 union all
select '20091107','033002',30 union all
select '20091107','033003',2 union all
select '20091108','033001',10.8 union all
select '20091108','033002',30 union all
select '20091108','033003',2;with CET as (
select a.* ,bitmap=case when a.[价格]-b.[价格]=0 then 1 else 0 end from (select *,row_number() over (order by 商品编码,日期) as ids from [tb] ) a
inner join
(select *,row_number() over (order by 商品编码,日期) + 1 as ids from [tb] ) bon a.商品编码=b.商品编码
and a.ids=b.ids
)select 商品编码,[价格],sum(bitmap)+1 as 连续天数
from CET
where bitmap=1
group by 商品编码,[价格]
order by 商品编码,[价格]select * from [tb] order by 商品编码,日期连续出现的。