是这样的,有一个表T, 字段A,B,C,D 按C,D排序后如下:
C D A B
c a d
c a e
c a f
c a g
c b d
c b d
c b e
c b f
c a a
c a b想输出如下格式
C D min(A),MAX(A)
c a d g
c b d e拿个实际点的例子,比如我一个数值值,每天取一次,我想知道数值相同的那个区间
比如
值 开始时间 结束时间
a 2007-1-1 2007-1-4
b 2007-1-5 2007-1-13
a 2007-1-14 2007-1-29
而原表可能是
a 2007-1-1
a 2007-1-2
a 2007-1-3
a 2007-1-4
b 2005-1-5
………………
C D A B
c a d
c a e
c a f
c a g
c b d
c b d
c b e
c b f
c a a
c a b想输出如下格式
C D min(A),MAX(A)
c a d g
c b d e拿个实际点的例子,比如我一个数值值,每天取一次,我想知道数值相同的那个区间
比如
值 开始时间 结束时间
a 2007-1-1 2007-1-4
b 2007-1-5 2007-1-13
a 2007-1-14 2007-1-29
而原表可能是
a 2007-1-1
a 2007-1-2
a 2007-1-3
a 2007-1-4
b 2005-1-5
………………
a 2007-1-1 2007-1-4
b 2007-1-5 2007-1-13
a 2007-1-14 2007-1-29
而原表可能是
a 2007-1-1
a 2007-1-2
a 2007-1-3
a 2007-1-4
b 2005-1-5
………………
---------------------
你的开始时间和结束时间如何判断,凭什么不是:值 开始时间 结束时间
a 2007-1-1 2007-1-2
a 2007-1-3 2007-1-4
create table tb (number varchar(6),日期 smalldatetime)
insert tb
select '001', '2006-01-01' union all
select '001', '2006-01-02' union all
select '001', '2006-01-03' union all
select '001', '2006-01-04' union all
select '001', '2006-01-05' union all
select '001', '2006-01-07' union all
select '001', '2006-01-08' union all
select '003', '2006-01-09' union all
select '003', '2006-01-10' union all
select '003', '2006-01-11'select number,min(日期) as 开始时间,max(日期) as 结束时间
from(
select a.number,a.日期,count(1) as ii
from tb a
join tb b on b.number=a.number and b.日期<=a.日期
group by a.number,a.日期
)c
group by number, DATEADD(day,-c.ii,日期) go
drop table tb/*
number 开始时间 结束时间
------ ------------------------------- ----------------------------------
001 2006-01-01 00:00:00 2006-01-05 00:00:00
001 2006-01-07 00:00:00 2006-01-08 00:00:00
003 2006-01-09 00:00:00 2006-01-11 00:00:00(3 row(s) affected)*/
set nocount on
declare @Test table (ID int identity(1, 1), C1 char(1), C2 char(10))
insert @Test
select 'a', '2007-01-01' union all
select 'b', '2007-01-02' union all
select 'b', '2007-01-03' union all
select 'c', '2007-01-04' union all
select 'c', '2007-01-05' union all
select 'c', '2007-01-06' union all
select 'd', '2007-01-07' union all
select 'a', '2007-01-08' union all
select 'a', '2007-01-09'declare @Min table (ID int identity(1, 1), C1 char(1), C2 char(10))
insert @Min select C1, C2 from (select a.C1, a.C2, Flag = case when a.C1 = b.C1 then 0 else 1 end from @Test a left join @Test b on a.ID = b.ID + 1) a where Flag = 1 order by C2, C1declare @Max table (ID int identity(1, 1), C1 char(1), C2 char(10))
insert @Max select C1, C2 from (select a.C1, a.C2, Flag = case when a.C1 = b.C1 then 0 else 1 end from @Test a left join @Test b on a.ID = b.ID - 1) a where Flag = 1 order by C2, C1select * from @Test
select 序号 = a.ID, 区间 = a.C1, 最小 = a.C2, 最大 = b.C2 from @Min a, @Max b where a.ID = b.ID
set nocount off/*ID C1 C2
----------- ---- ----------
1 a 2007-01-01
2 b 2007-01-02
3 b 2007-01-03
4 c 2007-01-04
5 c 2007-01-05
6 c 2007-01-06
7 d 2007-01-07
8 a 2007-01-08
9 a 2007-01-09序号 区间 最小 最大
----------- ---- ---------- ----------
1 a 2007-01-01 2007-01-01
2 b 2007-01-02 2007-01-03
3 c 2007-01-04 2007-01-06
4 d 2007-01-07 2007-01-07
5 a 2007-01-08 2007-01-09*/