Date ID Content
2012-10-25 1 test
2012-10-26 1 test
2012-10-27 1 test
2012-10-28 1 test
2012-10-29 1 test
2012-11-15 1 Test2
2012-11-16 1 Test2
2012-11-17 1 Test2
2012-11-18 1 Test2
2012-12-11 2 xx
2012-12-12 2 xx
2012-12-13 2 xx
2012-12-14 2 xx
2012-2-1 3 xxx
2012-2-2 3 xxx
2012-2-3 3 xxx
2012-2-9 3 xxxx
2012-2-10 3 xxxx
2012-2-11 3 xxxx
已知数据数据如下:根据上表数据,请教一个条sql语句能够查询得到下面的结果:
BeginDate EndDate ID Content
2012-10-25 2012-10-29 1 test
2012-11-15 2012-11-18 1 Test2
2012-12-11 2012-12-14 2 xx
2012-2-1 2012-2-3 3 xxx
2012-2-9 2012-2-11 3 xxxx
PS:连续日期的ID值和Content值是一样的
2012-10-25 1 test
2012-10-26 1 test
2012-10-27 1 test
2012-10-28 1 test
2012-10-29 1 test
2012-11-15 1 Test2
2012-11-16 1 Test2
2012-11-17 1 Test2
2012-11-18 1 Test2
2012-12-11 2 xx
2012-12-12 2 xx
2012-12-13 2 xx
2012-12-14 2 xx
2012-2-1 3 xxx
2012-2-2 3 xxx
2012-2-3 3 xxx
2012-2-9 3 xxxx
2012-2-10 3 xxxx
2012-2-11 3 xxxx
已知数据数据如下:根据上表数据,请教一个条sql语句能够查询得到下面的结果:
BeginDate EndDate ID Content
2012-10-25 2012-10-29 1 test
2012-11-15 2012-11-18 1 Test2
2012-12-11 2012-12-14 2 xx
2012-2-1 2012-2-3 3 xxx
2012-2-9 2012-2-11 3 xxxx
PS:连续日期的ID值和Content值是一样的
FROM TB GROUP BY ID ,Content
if(object_id('a')is not null) drop table a
go
create table a
(
date datetime,
id int,
content varchar(50)
)
go
insert into a
select '2012-10-25',1,'test' union all
select '2012-10-26',1,'test' union all
select '2012-10-27',1,'test' union all
select '2012-10-28',1,'test' union all
select '2012-10-29',1,'test' union all
select '2012-11-15',1,'Test2' union all
select '2012-11-16',1,'Test2' union all
select '2012-11-17',1,'Test2' union all
select '2012-11-18',1,'Test2' union all
select '2012-12-11',2,'xx' union all
select '2012-12-12',2,'xx' union all
select '2012-12-13',2,'xx' union all
select '2012-12-14',2,'xx' union all
select '2012-2-1',3,'xxx' union all
select '2012-2-2',3,'xxx' union all
select '2012-2-3',3,'xxx' union all
select '2012-2-9',3,'xxxx' union all
select '2012-2-10',3,'xxxx' union all
select '2012-2-11',3,'xxxx'select min(date)as startdate,max(date) as enddate,id,content
from a
group by id,content
/*
startdate enddate id content
----------------------- ----------------------- ----------- --------------------------------------------------
2012-10-25 00:00:00.000 2012-10-29 00:00:00.000 1 test
2012-11-15 00:00:00.000 2012-11-18 00:00:00.000 1 Test2
2012-12-11 00:00:00.000 2012-12-14 00:00:00.000 2 xx
2012-02-01 00:00:00.000 2012-02-03 00:00:00.000 3 xxx
2012-02-09 00:00:00.000 2012-02-11 00:00:00.000 3 xxxx(5 行受影响)
*/