Select B.* ,C.*
From(
Select 岗位,Min(日期) From Table1 Group By 岗位) B,(Select Max(日期) From Table1 Group By 岗位) C
From(
Select 岗位,Min(日期) From Table1 Group By 岗位) B,(Select Max(日期) From Table1 Group By 岗位) C
调试欢乐多
Select B.* ,C.*
From(
Select 岗位,Min(日期) From Table1 Group By 岗位) B Left Join (Select 岗位, Max(日期) From Table1 Group By 岗位) C On B.岗位=C.岗位
declare @a varchar(10),@b int
set @b=0
update #a set @b=case when @a=岗位 then @b else @b+1 end,@a=岗位,flag=@bselect 岗位,min(日期) 开始,max(日期) 结束 from #a group by flag,岗位go
drop table #a
insert table1 Select 'A',1
Union all select 'A',2
Union all select 'A',3
Union all select 'B',7
Union all select 'A',8
Union all select 'A',9
Union all select 'B',11
Union all select 'B',12
Union all select 'B',14
Union all select 'C',15
Union all select 'C',16
Select 岗位,min(日期),IsNull(日期2,(Select max(日期) from table1)) from
(Select 岗位,日期,(Select max(日期) from table1 where 日期<bb.日期1) as 日期2 from
(
Select 岗位,日期,(Select min(日期) from table1 where 岗位 <> a.岗位 and 日期 > a.日期) as 日期1 from table1 a
) bb
) cc
group by 岗位,日期2
order by min(日期)
岗位
---- ----------- -----------
A 1 3
B 7 7
A 8 9
B 11 14
C 15 16(所影响的行数为 5 行)