有数据表T，内容如下：
Card_ID Locator_ID StartTime EndTime
-----------------------------------------------------------------------
100 1 1 2
100 1 2 3
100 1 3 4
100 1 5 6
根据相同的EndTime、StartTime，合并为：
Card_ID Locator_ID StartTime EndTime
-----------------------------------------------------------------------
100 1 1 4
100 1 5 6
请大家帮忙！
------解决方案--------------------
create table T
(Card_ID int,Locator_ID int,StartTime int,EndTime int)
insert into T
select 100,1,1,2 union all
select 100,1,2,3 union all
select 100,1,3,4 union all
select 100,1,5,6
with v as
(select a.Card_ID,a.Locator_ID,a.StartTime,a.EndTime,a.StartTime 'x'
from T a
where not exists(select 1
from T b
where b.Card_ID=a.Card_ID and b.Locator_ID=a.Locator_ID
and b.EndTime=a.StartTime)
union all
select d.Card_ID,d.Locator_ID,d.StartTime,d.EndTime,c.x
from v c
inner join T d on c.Card_ID=d.Card_ID and c.Locator_ID=d.Locator_ID
and c.EndTime=d.StartTime)
select Card_ID,Locator_ID,min(StartTime) 'StartTime',max(EndTime) 'EndTime'
from v
group by Card_ID,Locator_ID,x
/*
Card_ID Locator_ID StartTime EndTime
----------- ----------- ----------- -----------
100 1 1 4
100 1 5 6
(2 row(s) affected)
*/
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-28 10:54:53
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Card_ID] int,[Locator_ID] int,[StartTime] int,[EndTime] int)
insert [huang]
select 100,1,1,2 union all
select 100,1,2,3 union all
select 100,1,3,4 union all
select 100,1,5,6
--------------开始查询--------------------------
;WITH cte AS (
select * ,ROW_NUMBER()OVER(PARTITION BY [Card_ID],[Locator_ID] ORDER BY [StartTime])oid
from [huang]),