27,582
社区成员
发帖
与我相关
我的任务
分享sql 求好方法解决
--不罗嗦了，下面有说明。
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
*/
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
*/
...全文
请发表友善的回复…
发表回复
samurai 2012-02-21
- 打赏
- 举报
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
*/
andysun88 2012-02-20
- 打赏
- 举报
汉口店 真维斯 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
--能不能合并呢？
andysun88 2012-02-20
- 打赏
- 举报
看看先
AcHerat 2012-02-20
- 打赏
- 举报
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 行受影响)
andysun88 2012-02-20
- 打赏
- 举报
最好是效率高一些的最好！期待各位大大。
andysun88 2012-02-20
- 打赏
- 举报
AcHerat 2012-02-20
- 打赏
- 举报
额。看错，等！
thinclient 2012-02-20
- 打赏
- 举报
强烈关注！
AcHerat 2012-02-20
- 打赏
- 举报
select *
from tb
where datediff(dd,startDatetime,enddatetime) > 15
JS 2012-02-20
- 打赏
- 举报
已经有答案了
如果是oracle的话，用lag或lead函数可以很容易得到结果
如果是oracle的话，用lag或lead函数可以很容易得到结果
andy_liucj 2012-02-20
- 打赏
- 举报
;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
andy_liucj 2012-02-20
- 打赏
- 举报
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)
勿勿 2012-02-20
- 打赏
- 举报
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
