首页新闻找找看学习计划

关于连续日期的sql语句怎么写?

0
悬赏园豆:10 [待解决问题]

已知表数据如下:

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

test

2012-11-16

1

test

2012-11-17

1

test

2012-11-18

1

test

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

xxx

2012-2-10

3

xxx

2012-2-11

3

xxx

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

根据上表数据,请教一个条sql语句能够查询得到下面的结果:

BeginDate

EndDate

ID

Content

2012-10-25

2012-10-29

1

test

2012-11-15

2012-11-18

1

test

2012-12-11

2012-12-14

2

xx

2012-2-1

2012-2-3

3

xxx

2012-2-9

2012-2-11

3

xxx

柠茶的主页 柠茶 | 初学一级 | 园豆:186
提问于:2012-11-21 21:26
< >
分享
所有回答(1)
0
declare @t table (Date datetime,ID int,Content varchar(4))
insert into @t
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,'test' union all
select '2012-11-16',1,'test' union all
select '2012-11-17',1,'test' union all
select '2012-11-18',1,'test' 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,'xxx' union all
select '2012-2-10',3,'xxx' union all
select '2012-2-11',3,'xxx'
 
;with maco as
(
    select 
        row_number() over (order by (select 1)) as rid,
        Date,ID,[Content],cast(Date as int) as dint 
    from @t
)
select 
    convert(varchar(10),min(Date),120) as BeginDate,
    convert(varchar(10),max(Date),120) as EndDate,
    ID,[Content] 
from maco group by ID,[Content],dint-rid
/*
BeginDate  EndDate    ID          Content
---------- ---------- ----------- -------
2012-10-25 2012-10-29 1           test
2012-11-15 2012-11-18 1           test
2012-12-11 2012-12-14 2           xx
2012-02-01 2012-02-03 3           xxx
2012-02-09 2012-02-11 3           xxx
*/
哈尔滨☆叶子 | 园豆:202 (菜鸟二级) | 2012-11-21 22:04

大神啊,这都能下个出来!赞!

支持(0) 反对(0) jone_e | 园豆:1410 (小虾三级) | 2012-11-22 09:24

不过还是提个建议:我想把row_number() over (order by (select 1)) as rid

换成 row_number() over (order by date) as rid 会比较好!

欢迎讨论!

支持(0) 反对(0) jone_e | 园豆:1410 (小虾三级) | 2012-11-22 09:31
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册