连续相同的记录 进行分组查询，恳求高手指点

[qiye176]

有记录表
address startime

aa 2010-1-2
aa 2010-1-3
aa 2010-1-4
bb 2010-1-5
bb 2010-1-6
cc 2010-1-7
aa 2010-1-10
aa 2010-1-11


需要结果
address mintime maxtime
aa 2010-1-2 2010-1-4
bb 2010-1-5 2010-1-6
cc 2010-1-7 2010-1-7
aa 2010-1-10 2010-1-11


需要将连续相同的记录分组，请问如何实现？

[yibey]

create table #t(address varchar(10),startime varchar(50))
insert into #t(address, startime)
select 
'aa', '2010-1-2' union select
'aa', '2010-1-3' union select
'aa', '2010-1-4' union select
'bb', '2010-1-5' union select
'bb', '2010-1-6' union select
'cc', '2010-1-7' union select
'aa', '2010-1-10' union select
'aa', '2010-1-11'


--插入临时表#tb
select identity(int,1,1)as id, 0 as num , address, cast(startime as datetime) startime into #tb from #t order by cast(startime as datetime)

--更新临时表num字段
declare @num int
set @num =1
update #tb set  num = @num ,
                @num = (case when (select address from #tb where id =a.id-1) <>a.address then @num+1 else @num end)
               
from #tb a

--查询所要的结果集

select address, min(startime) as mintime,max(startime) as maxtime   from #tb group by num , address  
order by mintime
--释放临时表
drop table #t
drop table #tb

/*
address    mintime                 maxtime
---------- ----------------------- -----------------------
aa         2010-01-02 00:00:00.000 2010-01-04 00:00:00.000
bb         2010-01-05 00:00:00.000 2010-01-06 00:00:00.000
cc         2010-01-07 00:00:00.000 2010-01-07 00:00:00.000
aa         2010-01-10 00:00:00.000 2010-01-11 00:00:00.000

(4 row(s) affected)

[hlf1989]

create table tb(address varchar(100), startime datetime)

insert tb 
select 'aa','2010-1-2' union 
select 'aa','2010-1-3' union 
select 'aa','2010-1-4' union 
select 'bb','2010-1-5' union 
select 'bb','2010-1-6' union 
select 'cc','2010-1-7' 

insert tb
select 'aa','2010-1-10' union 
select 'aa','2010-1-11' 


alter table tb add lb int
go
declare @var varchar(100),@n int
set @var='aa';set @n=1
update tb 
set  @n=case when @var=address then @n else @n+1 end,
@var=address,
lb=@n
go
select address=max(address),
       mintime=min(startime),
       maxtime=max(startime)
 from tb group by lb

go
drop table tb

[liang145]

create table #tb
(address nvarchar(10), startime nvarchar(10))
insert #tb
select 'aa' ,'2010-1-2' union all
select 'aa' ,'2010-1-3' union all
select 'aa' ,'2010-1-4' union all
select 'bb' ,'2010-1-5' union all
select 'bb' ,'2010-1-6' union all
select 'cc' ,'2010-1-7' union all
select 'aa' ,'2010-1-10' union all
select 'aa' ,'2010-1-11'

;with T as 
(select year(startime)+month(startime)+day(startime)-(Row_Number() over(partition by address order by cast(startime as datetime))) gid
,* from #tb)
select address,min(startime) as mintime,max(startime) as maxtime from T group by address ,gid

[小妹妹]

来围观的。。。

[bihai]

临时表是个办法，如果不用下面这个也可以！
另6楼的有点小缺陷，比如日期不连续时就会出错！
ID | State | Date

1 1 2010-11-01 08:08:08
2 1 2010-11-01 08:09:08
3 1 2010-11-01 08:10:08
4 1 2010-11-01 08:11:08
5 1 2010-11-01 08:12:08
6 1 2010-11-01 08:13:08
7 1 2010-11-01 08:14:08
8 0 2010-11-01 08:15:08
9 0 2010-11-01 08:16:08
10 0 2010-11-01 08:17:08
11 0 2010-11-01 08:18:08
12 0 2010-11-01 08:19:08
13 1 2010-11-01 08:20:08
14 1 2010-11-01 08:21:08
15 1 2010-11-01 08:22:08
16 1 2010-11-01 08:23:08

;with cte as
(select a.*,Case state when (select top 1 state from tb where Date<a.Date order by Date desc) then 0 else 1 end as T from
tb a),
cte2 as
(select id,state,Date,(select SUM(T) from cte where Date<=a.Date) as Group_No from cte a)

select Group_No,State,
(select MIN(Date) from cte2 where Group_No=a.Group_No) as StartDate,
(select Max(Date) from cte2 where Group_No=a.Group_No) as EndDate
from cte2 a group by Group_No,State

Group_No State StartDate EndDate
----------- ----------- ----------------------- -----------------------
1 1 2010-11-01 08:08:08.000 2010-11-01 08:14:08.000
2 0 2010-11-01 08:15:08.000 2010-11-01 08:19:08.000
3 1 2010-11-01 08:20:08.000 2010-11-01 08:23:08.000

(3 行受影响)

[Shawn]

IF OBJECT_ID('tempdb..#t', 'u') IS NOT NULL
	DROP TABLE #t
go
create table #t(address varchar(10),startime DATETIME)
insert into #t(address, startime)
select 
'aa', '2010-1-2' union all select
'aa', '2010-1-3' union all select
'aa', '2010-1-4' union all select
'bb', '2010-1-5' union all select
'bb', '2010-1-6' union all select
'cc', '2010-1-7' union all select
'aa', '2010-1-10' union all select
'aa', '2010-1-11'
GO
--SQL:
;WITH cte AS
(
	SELECT
		rowno = ROW_NUMBER() OVER(ORDER BY startime),
		groupno = ROW_NUMBER() OVER(PARTITION BY [address] ORDER BY startime),
		*
	FROM #t
)
SELECT
	[address],
	mintime=CONVERT(CHAR(10), MIN(startime), 120),
	maxtime=CONVERT(CHAR(10), MAX(startime), 120)
FROM cte
GROUP BY [address], rowno-groupno
ORDER BY MIN(startime)
/*
aa	2010-01-02	2010-01-04
bb	2010-01-05	2010-01-06
cc	2010-01-07	2010-01-07
aa	2010-01-10	2010-01-11
*/

[水向东流时光怎么偷]

学习，占位，收藏，学习！

[快溜]

select address,mintime=min(startime),maxtime=max(startime)
from tb group by address,left(startime,6),len(startime)

[longai123]

declare @var varchar(100),@n int
set @var='aa';set @n=1
update tb
set @n=case when @var=address then @n else @n+1 end,
@var=address,
lb=@n
go
select address=max(address),
mintime=min(startime),
maxtime=max(startime)
from tb group by address

[qiye176]

ssp2009 不行啊

[yibey]

[Quote=引用 1 楼 ssp2009 的回复:]

SQL code
select address,mintime=min(startime),maxtime=max(startime)
from tb group by address,left(startime,6),len(startime)
[/Quote]

哈哈1楼投机取巧

[gogodiy]

这个是典型的孤岛问题，MSSQL2005以上版本解法如下：

create table t1
(
	address1 varchar(10),
	starttime datetime
)
insert into t1
select 'aa', '2010-1-2' union all
select 'aa', '2010-1-3' union all
select 'aa', '2010-1-4' union all
select 'bb', '2010-1-5' union all
select 'bb', '2010-1-6' union all
select 'cc', '2010-1-7' union all
select 'aa', '2010-1-10' union all
select 'aa', '2010-1-11'
select * from t1

;with aaa as
(select ROW_NUMBER() over(order by (select 0)) as row,* from t1)
,bbb as
(select *,DATEADD(DAY,-row,starttime) as endtime from aaa)
select address1,MIN(starttime) as starttime,MAX(starttime) as endtime from bbb group by address1,endtime

address1 starttime endtime
aa 2010-01-02 00:00:00.000 2010-01-04 00:00:00.000
bb 2010-01-05 00:00:00.000 2010-01-06 00:00:00.000
cc 2010-01-07 00:00:00.000 2010-01-07 00:00:00.000
aa 2010-01-10 00:00:00.000 2010-01-11 00:00:00.000

[qiye176]

谢谢各位 受教了！！

[geass]

学习，占位，收藏，学习！