rego costcentername startdate enddate
AE70PV 51417 64702 2007-01-01 2007-09-17
AE70PV 51417 34504 2007-09-18 2100-01-01
AK11ZS 51417 34501 2007-01-01 2007-09-16
AK11ZS 51417 XX198 2007-09-17 2100-01-01
AK83HB 51417 66303 2007-02-27 2007-09-06
AK83HB 51417 34504 2007-09-07 2100-01-01
AM77KA 51417 34501 2007-01-01 2007-09-16
AM77KA 51417 XX198 2007-09-17 2100-01-01
AP32XJ 51417 27510 2007-06-28 2007-09-12
AP32XJ 51417 XX232 2007-09-13 2007-09-25
AP32XJ 51417 27510 2007-09-26 2100-01-01
AP98XI 51417 64302 2007-06-29 2007-09-17
AP98XI 51417 64702 2007-09-18 2100-01-01
AQ23AY 51417 XX254 2007-07-14 2007-09-11
AQ23AY 51417 27510 2007-09-12 2007-09-25
AQ23AY 51417 27503 2007-09-26 2100-01-01
这样一张表中如何通过sql语句把相同rego在指定时间段中所占天数多的数据提取出来
如：
（时间段为：2007.9.1～2007.9.30）
rego为：AE70PV， 有两条记录，一条在指定时间段占了16天，一条占了14天，只要把占16天的数据取出就可以了
------解决方案--------------------
- SQL code
declare @t table(rego varchar(10),costcentername varchar(20),startdate varchar(10),enddate varchar(10))insert into @t values('AE70PV','51417 64702','2007-01-01','2007-09-17') insert into @t values('AE70PV','51417 34504','2007-09-18','2100-01-01') insert into @t values('AK11ZS','51417 34501','2007-01-01','2007-09-16') insert into @t values('AK11ZS','51417 XX198','2007-09-17','2100-01-01') insert into @t values('AK83HB','51417 66303','2007-02-27','2007-09-06') insert into @t values('AK83HB','51417 34504','2007-09-07','2100-01-01') insert into @t values('AM77KA','51417 34501','2007-01-01','2007-09-16') insert into @t values('AM77KA','51417 XX198','2007-09-17','2100-01-01') insert into @t values('AP32XJ','51417 27510','2007-06-28','2007-09-12') insert into @t values('AP32XJ','51417 XX232','2007-09-13','2007-09-25') insert into @t values('AP32XJ','51417 27510','2007-09-26','2100-01-01') insert into @t values('AP98XI','51417 64302','2007-06-29','2007-09-17') insert into @t values('AP98XI','51417 64702','2007-09-18','2100-01-01') insert into @t values('AQ23AY','51417 XX254','2007-07-14','2007-09-11') insert into @t values('AQ23AY','51417 27510','2007-09-12','2007-09-25') insert into @t values('AQ23AY','51417 27503','2007-09-26','2100-01-01') declare @startdate datetime,@enddate datetimeset @startdate='2007-09-01'set @enddate ='2007-09-30'select t.*from @t twhere ((t.startdate between @startdate and @enddate) or (t.enddate between @startdate and @enddate)) and not exists(select 1 from @t where rego=t.rego and ((startdate between @startdate and @enddate) or (enddate between @startdate and @enddate)) and datediff(dd,case when startdate>@startdate then startdate else @startdate end, case when enddate <@enddate then enddate else @enddate end) > datediff(dd,case when t.startdate>@startdate then t.startdate else @startdate end, case when t.enddate <@enddate then t.enddate else @enddate end))/* rego costcentername startdate enddate ---------- -------------------- ---------- ---------- AE70PV 51417 64702 2007-01-01 2007-09-17AK11ZS 51417 34501 2007-01-01 2007-09-16AK83HB 51417 34504 2007-09-07 2100-01-01AM77KA 51417 34501 2007-01-01 2007-09-16AP32XJ 51417 XX232 2007-09-13 2007-09-25AP98XI 51417 64302 2007-06-29 2007-09-17AQ23AY 51417 27510 2007-09-12 2007-09-25*/