-
本版专家分：3578结帖率 92.86% -
--不罗嗦了，下面有说明。
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[Shopname] nvarchar(20),[goodname] nvarchar(20),[updatetime] Datetime)
Insert #T
select 1,N'武昌店',N'真维斯','2011-03-12 00:30:00' union all
select 2,N'武昌店',N'真维斯','2011-03-25 00:30:00' union all
select 3,N'武昌店',N'真维斯','2011-04-12 00:30:00' union all
select 4,N'武昌店',N'真维斯','2011-05-12 00:30:00' union all
select 5,N'武昌店',N'真维斯','2011-06-12 00:30:00' union all
select 6,N'武昌店',N'真维斯','2011-07-20 00:30:00' union all
select 7,N'武昌店',N'老人头','2011-03-12 00:30:00' union all
select 8,N'武昌店',N'老人头','2011-03-25 00:30:00' union all
select 9,N'武昌店',N'老人头','2011-04-12 00:30:00' union all
select 10,N'武昌店',N'老人头','2011-04-25 00:30:00' union all
select 11,N'武昌店',N'老人头','2011-07-20 00:30:00' union all
select 12,N'武昌店',N'班尼路','2011-03-12 00:30:00' union all
select 13,N'武昌店',N'班尼路','2011-04-25 00:30:00' union all
select 14,N'武昌店',N'班尼路','2011-05-12 00:30:00' union all
select 15,N'武昌店',N'班尼路','2011-07-20 00:30:00' union all
select 16,N'汉口店',N'真维斯','2011-03-12 00:30:00' union all
select 17,N'汉口店',N'真维斯','2011-03-25 00:30:00' union all
select 18,N'汉口店',N'真维斯','2011-04-12 00:30:00' union all
select 19,N'汉口店',N'真维斯','2011-05-12 00:30:00' union all
select 20,N'汉口店',N'真维斯','2011-06-12 00:30:00' union all
select 21,N'汉口店',N'真维斯','2011-07-20 00:30:00' union all
select 22,N'汉口店',N'老人头','2011-03-12 00:30:00' union all
select 23,N'汉口店',N'老人头','2011-03-25 00:30:00' union all
select 24,N'汉口店',N'老人头','2011-04-12 00:30:00' union all
select 25,N'汉口店',N'老人头','2011-04-25 00:30:00' union all
select 26,N'汉口店',N'老人头','2011-07-20 00:30:00' union all
select 27,N'汉阳店',N'真维斯','2011-03-12 00:30:00' union all
select 28,N'汉阳店',N'真维斯','2011-03-25 00:30:00' union all
select 29,N'汉阳店',N'真维斯','2011-04-12 00:30:00' union all
select 30,N'汉阳店',N'真维斯','2011-05-12 00:30:00' union all
select 31,N'汉阳店',N'真维斯','2011-06-12 00:30:00' union all
select 32,N'汉阳店',N'真维斯','2011-07-20 00:30:00' union all
select 33,N'汉阳店',N'老人头','2011-03-12 00:30:00' union all
select 34,N'汉阳店',N'老人头','2011-03-25 00:30:00' union all
select 35,N'汉阳店',N'老人头','2011-04-12 00:30:00' union all
select 36,N'汉阳店',N'老人头','2011-04-25 00:30:00' union all
select 37,N'汉阳店',N'老人头','2011-07-20 00:30:00'
Go
--我要得到的结果是：
--各个分店中各类服装进货时间间隔大于15天的记录，得到的结果硬是
/*
Shopname goodname startDatetime enddatetime
武昌店 真维斯 2011-03-25 00:30:00 2011-07-20 00:30:00
武昌店 老人头 2011-03-25 00:30:00 2011-04-12 00:30:00
武昌店 老人头 2011-04-25 00:30:00 2011-07-20 00:30:00
武昌店 班尼路 2011-04-25 00:30:00 2011-07-20 00:30:00
汉口店 真维斯 2011-03-25 00:30:00 2011-07-20 00:30:00
汉口店 老人头 2011-03-25 00:30:00 2011-04-12 00:30:00
汉口店 老人头 2011-04-25 00:30:00 2011-07-20 00:30:00
汉阳店 真维斯 2011-03-25 00:30:00 2011-07-20 00:30:00
汉阳店 老人头 2011-03-25 00:30:00 2011-04-12 00:30:00
汉阳店 老人头 2011-04-25 00:30:00 2011-07-20 00:30:00
*/
![]()
-
本版专家分：97841
-
- 进士 2011年 总版技术专家分年内排行榜第十
-
-
银牌
2011年8月 总版技术专家分月排行榜第二
2011年7月 总版技术专家分月排行榜第二
-
- 微软MVP 2012年7月 荣获微软MVP称号
-
-
红花
2011年8月 MS-SQL Server大版内专家分月排行榜第一
2011年7月 MS-SQL Server大版内专家分月排行榜第一
-
-
select *
from tb
where datediff(dd,startDatetime,enddatetime) > 15
-
本版专家分：2283
-
强烈关注！
-
本版专家分：97841
-
- 进士 2011年 总版技术专家分年内排行榜第十
-
-
银牌
2011年8月 总版技术专家分月排行榜第二
2011年7月 总版技术专家分月排行榜第二
-
- 微软MVP 2012年7月 荣获微软MVP称号
-
-
红花
2011年8月 MS-SQL Server大版内专家分月排行榜第一
2011年7月 MS-SQL Server大版内专家分月排行榜第一
-
-
额。看错，等！
-
本版专家分：3578
-
-
本版专家分：3578
-
最好是效率高一些的最好！期待各位大大。
-
本版专家分：97841
-
- 进士 2011年 总版技术专家分年内排行榜第十
-
-
银牌
2011年8月 总版技术专家分月排行榜第二
2011年7月 总版技术专家分月排行榜第二
-
- 微软MVP 2012年7月 荣获微软MVP称号
-
-
红花
2011年8月 MS-SQL Server大版内专家分月排行榜第一
2011年7月 MS-SQL Server大版内专家分月排行榜第一
-
-
Create table #T([id] int,[Shopname] nvarchar(20),[goodname] nvarchar(20),[updatetime] Datetime)
Insert #T
select 1,N'武昌店',N'真维斯','2011-03-12 00:30:00' union all
select 2,N'武昌店',N'真维斯','2011-03-25 00:30:00' union all
select 3,N'武昌店',N'真维斯','2011-04-12 00:30:00' union all
select 4,N'武昌店',N'真维斯','2011-05-12 00:30:00' union all
select 5,N'武昌店',N'真维斯','2011-06-12 00:30:00' union all
select 6,N'武昌店',N'真维斯','2011-07-20 00:30:00' union all
select 7,N'武昌店',N'老人头','2011-03-12 00:30:00' union all
select 8,N'武昌店',N'老人头','2011-03-25 00:30:00' union all
select 9,N'武昌店',N'老人头','2011-04-12 00:30:00' union all
select 10,N'武昌店',N'老人头','2011-04-25 00:30:00' union all
select 11,N'武昌店',N'老人头','2011-07-20 00:30:00' union all
select 12,N'武昌店',N'班尼路','2011-03-12 00:30:00' union all
select 13,N'武昌店',N'班尼路','2011-04-25 00:30:00' union all
select 14,N'武昌店',N'班尼路','2011-05-12 00:30:00' union all
select 15,N'武昌店',N'班尼路','2011-07-20 00:30:00' union all
select 16,N'汉口店',N'真维斯','2011-03-12 00:30:00' union all
select 17,N'汉口店',N'真维斯','2011-03-25 00:30:00' union all
select 18,N'汉口店',N'真维斯','2011-04-12 00:30:00' union all
select 19,N'汉口店',N'真维斯','2011-05-12 00:30:00' union all
select 20,N'汉口店',N'真维斯','2011-06-12 00:30:00' union all
select 21,N'汉口店',N'真维斯','2011-07-20 00:30:00' union all
select 22,N'汉口店',N'老人头','2011-03-12 00:30:00' union all
select 23,N'汉口店',N'老人头','2011-03-25 00:30:00' union all
select 24,N'汉口店',N'老人头','2011-04-12 00:30:00' union all
select 25,N'汉口店',N'老人头','2011-04-25 00:30:00' union all
select 26,N'汉口店',N'老人头','2011-07-20 00:30:00' union all
select 27,N'汉阳店',N'真维斯','2011-03-12 00:30:00' union all
select 28,N'汉阳店',N'真维斯','2011-03-25 00:30:00' union all
select 29,N'汉阳店',N'真维斯','2011-04-12 00:30:00' union all
select 30,N'汉阳店',N'真维斯','2011-05-12 00:30:00' union all
select 31,N'汉阳店',N'真维斯','2011-06-12 00:30:00' union all
select 32,N'汉阳店',N'真维斯','2011-07-20 00:30:00' union all
select 33,N'汉阳店',N'老人头','2011-03-12 00:30:00' union all
select 34,N'汉阳店',N'老人头','2011-03-25 00:30:00' union all
select 35,N'汉阳店',N'老人头','2011-04-12 00:30:00' union all
select 36,N'汉阳店',N'老人头','2011-04-25 00:30:00' union all
select 37,N'汉阳店',N'老人头','2011-07-20 00:30:00'
Go
;with cte as
(
select *,rid=row_number() over (partition by [Shopname],[goodname] order by [updatetime],id)
from #T
),ach as
(
select *
from cte t
where exists (select 1 from cte where [Shopname]=t.[Shopname] and [goodname]=t.[goodname]
and datediff(dd,t.updatetime,updatetime)>15 and rid=t.rid+1)
union
select *
from cte t
where exists (select 1 from cte where [Shopname]=t.[Shopname] and [goodname]=t.[goodname]
and datediff(dd,updatetime,t.updatetime)>15 and t.rid=rid+1)
),art as
(
select *,pid=row_number() over (partition by [Shopname],[goodname] order by [updatetime])
from ach
)
select a.[Shopname],a.[goodname],a.updatetime startdate,b.updatetime enddate
from art a join art b on a.pid + 1 = b.pid
and a.[Shopname]=b.[Shopname] and a.[goodname]=b.[goodname]
where datediff(dd,a.updatetime,b.updatetime) > 15
order by [Shopname],[goodname]
drop table #T
/***************************
Shopname goodname startdate enddate
-------------------- -------------------- ----------------------- -----------------------
汉口店 老人头 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉口店 老人头 2011-04-25 00:30:00.000 2011-07-20 00:30:00.000
汉口店 真维斯 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉口店 真维斯 2011-04-12 00:30:00.000 2011-05-12 00:30:00.000
汉口店 真维斯 2011-05-12 00:30:00.000 2011-06-12 00:30:00.000
汉口店 真维斯 2011-06-12 00:30:00.000 2011-07-20 00:30:00.000
汉阳店 老人头 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉阳店 老人头 2011-04-25 00:30:00.000 2011-07-20 00:30:00.000
汉阳店 真维斯 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉阳店 真维斯 2011-04-12 00:30:00.000 2011-05-12 00:30:00.000
汉阳店 真维斯 2011-05-12 00:30:00.000 2011-06-12 00:30:00.000
汉阳店 真维斯 2011-06-12 00:30:00.000 2011-07-20 00:30:00.000
武昌店 班尼路 2011-03-12 00:30:00.000 2011-04-25 00:30:00.000
武昌店 班尼路 2011-04-25 00:30:00.000 2011-05-12 00:30:00.000
武昌店 班尼路 2011-05-12 00:30:00.000 2011-07-20 00:30:00.000
武昌店 老人头 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
武昌店 老人头 2011-04-25 00:30:00.000 2011-07-20 00:30:00.000
武昌店 真维斯 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
武昌店 真维斯 2011-04-12 00:30:00.000 2011-05-12 00:30:00.000
武昌店 真维斯 2011-05-12 00:30:00.000 2011-06-12 00:30:00.000
武昌店 真维斯 2011-06-12 00:30:00.000 2011-07-20 00:30:00.000
(21 行受影响)
-
本版专家分：3578
-
看看先
-
本版专家分：3578
-
汉口店 真维斯 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉口店 真维斯 2011-04-12 00:30:00.000 2011-05-12 00:30:00.000
汉口店 真维斯 2011-05-12 00:30:00.000 2011-06-12 00:30:00.000
汉口店 真维斯 2011-06-12 00:30:00.000 2011-07-20 00:30:00.000
--能不能合并呢？
-
本版专家分：13507 -
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[Shopname] nvarchar(20),[goodname] nvarchar(20),[updatetime] Datetime)
Insert #T
select 1,N'武昌店',N'真维斯','2011-03-12 00:30:00' union all
select 2,N'武昌店',N'真维斯','2011-03-25 00:30:00' union all
select 3,N'武昌店',N'真维斯','2011-04-12 00:30:00' union all
select 4,N'武昌店',N'真维斯','2011-05-12 00:30:00' union all
select 5,N'武昌店',N'真维斯','2011-06-12 00:30:00' union all
select 6,N'武昌店',N'真维斯','2011-07-20 00:30:00' union all
select 7,N'武昌店',N'老人头','2011-03-12 00:30:00' union all
select 8,N'武昌店',N'老人头','2011-03-25 00:30:00' union all
select 9,N'武昌店',N'老人头','2011-04-12 00:30:00' union all
select 10,N'武昌店',N'老人头','2011-04-25 00:30:00' union all
select 11,N'武昌店',N'老人头','2011-07-20 00:30:00' union all
select 12,N'武昌店',N'班尼路','2011-03-12 00:30:00' union all
select 13,N'武昌店',N'班尼路','2011-04-25 00:30:00' union all
select 14,N'武昌店',N'班尼路','2011-05-12 00:30:00' union all
select 15,N'武昌店',N'班尼路','2011-07-20 00:30:00' union all
select 16,N'汉口店',N'真维斯','2011-03-12 00:30:00' union all
select 17,N'汉口店',N'真维斯','2011-03-25 00:30:00' union all
select 18,N'汉口店',N'真维斯','2011-04-12 00:30:00' union all
select 19,N'汉口店',N'真维斯','2011-05-12 00:30:00' union all
select 20,N'汉口店',N'真维斯','2011-06-12 00:30:00' union all
select 21,N'汉口店',N'真维斯','2011-07-20 00:30:00' union all
select 22,N'汉口店',N'老人头','2011-03-12 00:30:00' union all
select 23,N'汉口店',N'老人头','2011-03-25 00:30:00' union all
select 24,N'汉口店',N'老人头','2011-04-12 00:30:00' union all
select 25,N'汉口店',N'老人头','2011-04-25 00:30:00' union all
select 26,N'汉口店',N'老人头','2011-07-20 00:30:00' union all
select 27,N'汉阳店',N'真维斯','2011-03-12 00:30:00' union all
select 28,N'汉阳店',N'真维斯','2011-03-25 00:30:00' union all
select 29,N'汉阳店',N'真维斯','2011-04-12 00:30:00' union all
select 30,N'汉阳店',N'真维斯','2011-05-12 00:30:00' union all
select 31,N'汉阳店',N'真维斯','2011-06-12 00:30:00' union all
select 32,N'汉阳店',N'真维斯','2011-07-20 00:30:00' union all
select 33,N'汉阳店',N'老人头','2011-03-12 00:30:00' union all
select 34,N'汉阳店',N'老人头','2011-03-25 00:30:00' union all
select 35,N'汉阳店',N'老人头','2011-04-12 00:30:00' union all
select 36,N'汉阳店',N'老人头','2011-04-25 00:30:00' union all
select 37,N'汉阳店',N'老人头','2011-07-20 00:30:00'
Go
--我要得到的结果是：
--各个分店中各类服装进货时间间隔大于15天的记录，得到的结果硬是
/*
Shopname goodname startDatetime enddatetime
武昌店 真维斯 2011-03-25 00:30:00 2011-07-20 00:30:00
武昌店 老人头 2011-03-25 00:30:00 2011-04-12 00:30:00
武昌店 老人头 2011-04-25 00:30:00 2011-07-20 00:30:00
武昌店 班尼路 2011-04-25 00:30:00 2011-07-20 00:30:00
汉口店 真维斯 2011-03-25 00:30:00 2011-07-20 00:30:00
汉口店 老人头 2011-03-25 00:30:00 2011-04-12 00:30:00
汉口店 老人头 2011-04-25 00:30:00 2011-07-20 00:30:00
汉阳店 真维斯 2011-03-25 00:30:00 2011-07-20 00:30:00
汉阳店 老人头 2011-03-25 00:30:00 2011-04-12 00:30:00
汉阳店 老人头 2011-04-25 00:30:00 2011-07-20 00:30:00
*/
select * from (
select Shopname,goodname,updatetime as startDatetime ,
(select updatetime from #T where Shopname=t.Shopname and goodname=t.goodname and id-t.id=1 and
DATEDIFF(DD,t.updatetime, updatetime)>15
)as enddatetime from #T t )s where enddatetime is not NULL
Shopname goodname startDatetime enddatetime
-------------------- -------------------- ----------------------- -----------------------
武昌店 真维斯 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
武昌店 真维斯 2011-04-12 00:30:00.000 2011-05-12 00:30:00.000
武昌店 真维斯 2011-05-12 00:30:00.000 2011-06-12 00:30:00.000
武昌店 真维斯 2011-06-12 00:30:00.000 2011-07-20 00:30:00.000
武昌店 老人头 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
武昌店 老人头 2011-04-25 00:30:00.000 2011-07-20 00:30:00.000
武昌店 班尼路 2011-03-12 00:30:00.000 2011-04-25 00:30:00.000
武昌店 班尼路 2011-04-25 00:30:00.000 2011-05-12 00:30:00.000
武昌店 班尼路 2011-05-12 00:30:00.000 2011-07-20 00:30:00.000
汉口店 真维斯 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉口店 真维斯 2011-04-12 00:30:00.000 2011-05-12 00:30:00.000
汉口店 真维斯 2011-05-12 00:30:00.000 2011-06-12 00:30:00.000
汉口店 真维斯 2011-06-12 00:30:00.000 2011-07-20 00:30:00.000
汉口店 老人头 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉口店 老人头 2011-04-25 00:30:00.000 2011-07-20 00:30:00.000
汉阳店 真维斯 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉阳店 真维斯 2011-04-12 00:30:00.000 2011-05-12 00:30:00.000
汉阳店 真维斯 2011-05-12 00:30:00.000 2011-06-12 00:30:00.000
汉阳店 真维斯 2011-06-12 00:30:00.000 2011-07-20 00:30:00.000
汉阳店 老人头 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉阳店 老人头 2011-04-25 00:30:00.000 2011-07-20 00:30:00.000
-
本版专家分：1720 -
Create table #T([id] int,[Shopname] nvarchar(20),[goodname] nvarchar(20),[updatetime] Datetime)
Insert #T
select 1,N'武昌店',N'真维斯','2011-03-12 00:30:00' union all
select 2,N'武昌店',N'真维斯','2011-03-25 00:30:00' union all
select 3,N'武昌店',N'真维斯','2011-04-12 00:30:00' union all
select 4,N'武昌店',N'真维斯','2011-05-12 00:30:00' union all
select 5,N'武昌店',N'真维斯','2011-06-12 00:30:00' union all
select 6,N'武昌店',N'真维斯','2011-07-20 00:30:00' union all
select 7,N'武昌店',N'老人头','2011-03-12 00:30:00' union all
select 8,N'武昌店',N'老人头','2011-03-25 00:30:00' union all
select 9,N'武昌店',N'老人头','2011-04-12 00:30:00' union all
select 10,N'武昌店',N'老人头','2011-04-25 00:30:00' union all
select 11,N'武昌店',N'老人头','2011-07-20 00:30:00' union all
select 12,N'武昌店',N'班尼路','2011-03-12 00:30:00' union all
select 13,N'武昌店',N'班尼路','2011-04-25 00:30:00' union all
select 14,N'武昌店',N'班尼路','2011-05-12 00:30:00' union all
select 15,N'武昌店',N'班尼路','2011-07-20 00:30:00' union all
select 16,N'汉口店',N'真维斯','2011-03-12 00:30:00' union all
select 17,N'汉口店',N'真维斯','2011-03-25 00:30:00' union all
select 18,N'汉口店',N'真维斯','2011-04-12 00:30:00' union all
select 19,N'汉口店',N'真维斯','2011-05-12 00:30:00' union all
select 20,N'汉口店',N'真维斯','2011-06-12 00:30:00' union all
select 21,N'汉口店',N'真维斯','2011-07-20 00:30:00' union all
select 22,N'汉口店',N'老人头','2011-03-12 00:30:00' union all
select 23,N'汉口店',N'老人头','2011-03-25 00:30:00' union all
select 24,N'汉口店',N'老人头','2011-04-12 00:30:00' union all
select 25,N'汉口店',N'老人头','2011-04-25 00:30:00' union all
select 26,N'汉口店',N'老人头','2011-07-20 00:30:00' union all
select 27,N'汉阳店',N'真维斯','2011-03-12 00:30:00' union all
select 28,N'汉阳店',N'真维斯','2011-03-25 00:30:00' union all
select 29,N'汉阳店',N'真维斯','2011-04-12 00:30:00' union all
select 30,N'汉阳店',N'真维斯','2011-05-12 00:30:00' union all
select 31,N'汉阳店',N'真维斯','2011-06-12 00:30:00' union all
select 32,N'汉阳店',N'真维斯','2011-07-20 00:30:00' union all
select 33,N'汉阳店',N'老人头','2011-03-12 00:30:00' union all
select 34,N'汉阳店',N'老人头','2011-03-25 00:30:00' union all
select 35,N'汉阳店',N'老人头','2011-04-12 00:30:00' union all
select 36,N'汉阳店',N'老人头','2011-04-25 00:30:00' union all
select 37,N'汉阳店',N'老人头','2011-07-20 00:30:00'
Go
;with t as
(
select sn=row_number() over(partition by [Shopname], goodname order by [Shopname], goodname), * from #T
)
SELECT a.[Shopname], a.goodname, a.updatetime starttime, b.updatetime endtime
FROM t a join t b on a.[Shopname]=b.[Shopname] and a.[goodname]=b.[goodname] and a.sn=b.sn -1
where datediff(dd,a.updatetime,b.updatetime)>15
Shopname goodname starttime endtime
-------------------- -------------------- ----------------------- -----------------------
汉口店 老人头 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉口店 老人头 2011-04-25 00:30:00.000 2011-07-20 00:30:00.000
汉口店 真维斯 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉口店 真维斯 2011-04-12 00:30:00.000 2011-05-12 00:30:00.000
汉口店 真维斯 2011-05-12 00:30:00.000 2011-06-12 00:30:00.000
汉口店 真维斯 2011-06-12 00:30:00.000 2011-07-20 00:30:00.000
汉阳店 老人头 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉阳店 老人头 2011-04-25 00:30:00.000 2011-07-20 00:30:00.000
汉阳店 真维斯 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
汉阳店 真维斯 2011-04-12 00:30:00.000 2011-05-12 00:30:00.000
汉阳店 真维斯 2011-05-12 00:30:00.000 2011-06-12 00:30:00.000
汉阳店 真维斯 2011-06-12 00:30:00.000 2011-07-20 00:30:00.000
武昌店 班尼路 2011-03-12 00:30:00.000 2011-04-25 00:30:00.000
武昌店 班尼路 2011-04-25 00:30:00.000 2011-05-12 00:30:00.000
武昌店 班尼路 2011-05-12 00:30:00.000 2011-07-20 00:30:00.000
武昌店 老人头 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
武昌店 老人头 2011-04-25 00:30:00.000 2011-07-20 00:30:00.000
武昌店 真维斯 2011-03-25 00:30:00.000 2011-04-12 00:30:00.000
武昌店 真维斯 2011-04-12 00:30:00.000 2011-05-12 00:30:00.000
武昌店 真维斯 2011-05-12 00:30:00.000 2011-06-12 00:30:00.000
武昌店 真维斯 2011-06-12 00:30:00.000 2011-07-20 00:30:00.000
(21 row(s) affected)
-
本版专家分：1720
-
根据updatetime排序，上面的可能会有问题
;with t as
(
select sn=row_number() over(partition by [Shopname], goodname order by updatetime), * from #T
)
SELECT a.[Shopname], a.goodname, a.updatetime starttime, b.updatetime endtime
FROM t a join t b on a.[Shopname]=b.[Shopname] and a.[goodname]=b.[goodname] and a.sn=b.sn -1
where datediff(dd,a.updatetime,b.updatetime)>15
-
本版专家分：118 -
declare @t table( [id] int,[Shopname] nvarchar(20),[goodname] nvarchar(20),[updatetime] Datetime)
Insert @t
select 1,N'武昌店',N'真维斯','2011-03-12 00:30:00' union all
select 2,N'武昌店',N'真维斯','2011-03-25 00:30:00' union all
select 3,N'武昌店',N'真维斯','2011-04-12 00:30:00' union all
select 4,N'武昌店',N'真维斯','2011-05-12 00:30:00' union all
select 5,N'武昌店',N'真维斯','2011-06-12 00:30:00' union all
select 6,N'武昌店',N'真维斯','2011-07-20 00:30:00' union all
select 7,N'武昌店',N'老人头','2011-03-12 00:30:00' union all
select 8,N'武昌店',N'老人头','2011-03-25 00:30:00' union all
select 9,N'武昌店',N'老人头','2011-04-12 00:30:00' union all
select 10,N'武昌店',N'老人头','2011-04-25 00:30:00' union all
select 11,N'武昌店',N'老人头','2011-07-20 00:30:00' union all
select 12,N'武昌店',N'班尼路','2011-03-12 00:30:00' union all
select 13,N'武昌店',N'班尼路','2011-04-25 00:30:00' union all
select 14,N'武昌店',N'班尼路','2011-05-12 00:30:00' union all
select 15,N'武昌店',N'班尼路','2011-07-20 00:30:00' union all
select 16,N'汉口店',N'真维斯','2011-03-12 00:30:00' union all
select 17,N'汉口店',N'真维斯','2011-03-25 00:30:00' union all
select 18,N'汉口店',N'真维斯','2011-04-12 00:30:00' union all
select 19,N'汉口店',N'真维斯','2011-05-12 00:30:00' union all
select 20,N'汉口店',N'真维斯','2011-06-12 00:30:00' union all
select 21,N'汉口店',N'真维斯','2011-07-20 00:30:00' union all
select 22,N'汉口店',N'老人头','2011-03-12 00:30:00' union all
select 23,N'汉口店',N'老人头','2011-03-25 00:30:00' union all
select 24,N'汉口店',N'老人头','2011-04-12 00:30:00' union all
select 25,N'汉口店',N'老人头','2011-04-25 00:30:00' union all
select 26,N'汉口店',N'老人头','2011-07-20 00:30:00' union all
select 27,N'汉阳店',N'真维斯','2011-03-12 00:30:00' union all
select 28,N'汉阳店',N'真维斯','2011-03-25 00:30:00' union all
select 29,N'汉阳店',N'真维斯','2011-04-12 00:30:00' union all
select 30,N'汉阳店',N'真维斯','2011-05-12 00:30:00' union all
select 31,N'汉阳店',N'真维斯','2011-06-12 00:30:00' union all
select 32,N'汉阳店',N'真维斯','2011-07-20 00:30:00' union all
select 33,N'汉阳店',N'老人头','2011-03-12 00:30:00' union all
select 34,N'汉阳店',N'老人头','2011-03-25 00:30:00' union all
select 35,N'汉阳店',N'老人头','2011-04-12 00:30:00' union all
select 36,N'汉阳店',N'老人头','2011-04-25 00:30:00' union all
select 37,N'汉阳店',N'老人头','2011-07-20 00:30:00'
select t1.* from @t t1 where
datediff(day,t1.updatetime,(select top 1 updatetime from @t where updatetime>t1.updatetime
and Shopname=t1.Shopname))>15
/*
id Shopname goodname updatetime
2 武昌店 真维斯 2011-03-25 00:30:00.000
3 武昌店 真维斯 2011-04-12 00:30:00.000
4 武昌店 真维斯 2011-05-12 00:30:00.000
5 武昌店 真维斯 2011-06-12 00:30:00.000
8 武昌店 老人头 2011-03-25 00:30:00.000
9 武昌店 老人头 2011-04-12 00:30:00.000
10 武昌店 老人头 2011-04-25 00:30:00.000
13 武昌店 班尼路 2011-04-25 00:30:00.000
14 武昌店 班尼路 2011-05-12 00:30:00.000
17 汉口店 真维斯 2011-03-25 00:30:00.000
18 汉口店 真维斯 2011-04-12 00:30:00.000
19 汉口店 真维斯 2011-05-12 00:30:00.000
20 汉口店 真维斯 2011-06-12 00:30:00.000
23 汉口店 老人头 2011-03-25 00:30:00.000
24 汉口店 老人头 2011-04-12 00:30:00.000
25 汉口店 老人头 2011-04-25 00:30:00.000
28 汉阳店 真维斯 2011-03-25 00:30:00.000
29 汉阳店 真维斯 2011-04-12 00:30:00.000
30 汉阳店 真维斯 2011-05-12 00:30:00.000
31 汉阳店 真维斯 2011-06-12 00:30:00.000
34 汉阳店 老人头 2011-03-25 00:30:00.000
35 汉阳店 老人头 2011-04-12 00:30:00.000
36 汉阳店 老人头 2011-04-25 00:30:00.000
*/
