sql 语句觉得难不会
测试数据
nameupdatetime
a2012-03-08
a2012-03-09
a2012-03-17
a2012-03-18
b2012-03-14
b2012-03-15
b2012-03-16
b2012-03-17
b2012-03-18
b2012-03-19
b2012-03-20
b2012-03-21
c2012-03-12
c2012-03-13
c2012-03-15
c2012-03-16
c2012-03-19
c2012-03-20
c2012-03-21
结果
namemintimemaxtime
a2012-03-082012-03-09
a2012-03-172012-03-18
b2012-03-14NULL
c2012-03-122012-03-13
c2012-03-152012-03-16
c2012-03-19NULL
连续时间段取最大，最小时间
如果当前时间还有这个name，那么就最大时间为空
[解决办法]
- SQL code
--> 测试数据：[tbl]goif object_id('[tbl]') is not null drop table [tbl]gocreate table [tbl]([name] varchar(1),[updatetime] datetime)goinsert [tbl]select 'a','2012-03-08' union allselect 'a','2012-03-09' union allselect 'a','2012-03-17' union allselect 'a','2012-03-18' union allselect 'b','2012-03-14' union allselect 'b','2012-03-15' union allselect 'b','2012-03-16' union allselect 'b','2012-03-17' union allselect 'b','2012-03-18' union allselect 'b','2012-03-19' union allselect 'b','2012-03-20' union allselect 'b','2012-03-21' union allselect 'c','2012-03-12' union allselect 'c','2012-03-13' union allselect 'c','2012-03-15' union allselect 'c','2012-03-16' union allselect 'c','2012-03-19' union allselect 'c','2012-03-20' union allselect 'c','2012-03-21';with t as(select ROW_NUMBER()over(PARTITION by name order by updatetime) as id,* from tbl a where not exists(select 1 from tbl b where b.updatetime=DATEADD(DD,-1,a.updatetime) and b.name=a.name)),m as(select ROW_NUMBER()over(PARTITION by name order by updatetime) as id,* from tbl a where not exists(select 1 from tbl b where b.updatetime=DATEADD(DD,1,a.updatetime) and b.name=a.name))select t.name,t.updatetime as mindate,(case when convert(varchar(10),m.updatetime,120)=convert(varchar(10),GETDATE(),120) then null else m.updatetime end) as maxdatefrom t inner join m on t.id=m.id and t.name=m.name/*name mindate maxdatea 2012-03-08 00:00:00.000 2012-03-09 00:00:00.000a 2012-03-17 00:00:00.000 2012-03-18 00:00:00.000b 2012-03-14 00:00:00.000 NULLc 2012-03-12 00:00:00.000 2012-03-13 00:00:00.000c 2012-03-15 00:00:00.000 2012-03-16 00:00:00.000c 2012-03-19 00:00:00.000 NULL*/第一次解决这样的问题，不知道是不是你要的结果