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