排序后聚集 各位帮忙
是这样的，有一个表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
………………


------解决方案--------------------
--是这个意思么？


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)

*/

------解决方案--------------------
--测试数据——必须要有按C2、C1顺序排列的连续ID
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, C1

declare @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, C1

select * from @Test
select 序号 = a.ID, 区间 = a.C1, 最小 = a.C2, 最大 = b.C2 from @Min a, @Max b where a.ID = b.ID
set nocount off

/*