日期不间断查询(请高手帮忙)

duanzhi1984 2010-03-10 04:07:44 
declare @table table (name varchar(10),[date] varchar(10))



insert into @table

 select 'X106','2010-01-01' union all 

 select 'X106','2010-01-02' union all 

 select 'X106','2010-01-03' union all 

 select 'X106','2010-01-09' union all 

 select 'X106','2010-01-10' union all 

 select 'X106','2010-01-16' union all 

 select 'X106','2010-01-17' union all 

 select 'X106','2010-01-23' union all 

 select 'X106','2010-01-24' union all 

 select 'X106','2010-01-30' union all 

 select 'X106','2010-01-31' union all 

 select 'X106','2010-02-04' union all 

 select 'X106','2010-02-05' union all 

 select 'X106','2010-02-06' union all 

 select 'X106','2010-02-07' union all 

 select 'X106','2010-02-08' union all 

 select 'X106','2010-02-09' union all 

 select 'X106','2010-02-10' union all 

 select 'X106','2010-02-11' union all 

 select 'X106','2010-02-12' union all 

 select 'X106','2010-02-13' union all 

 select 'X106','2010-02-14' union all 

 select 'X106','2010-02-15' union all 

 select 'X106','2010-02-16' union all 

 select 'X106','2010-02-17' union all 

 select 'X106','2010-02-18' union all 

 select 'X106','2010-02-19' union all 

 select 'X106','2010-02-20' union all 

 select 'X106','2010-02-21' union all 

 select 'X106','2010-02-22' union all 

 select 'X106','2010-02-23' union all 

 select 'X106','2010-02-24' union all 

 select 'X106','2010-02-25' union all 

 select 'X106','2010-02-27' 

select *from @table

--问题：找到不间断的日期

--以上日期返回为

/*

X106	2010-02-04

X106	2010-02-05

X106	2010-02-06

X106	2010-02-07

X106	2010-02-08

X106	2010-02-09

X106	2010-02-10

X106	2010-02-11

X106	2010-02-12

X106	2010-02-13

X106	2010-02-14

X106	2010-02-15

X106	2010-02-16

X106	2010-02-17

X106	2010-02-18

X106	2010-02-19

X106	2010-02-20

X106	2010-02-21

X106	2010-02-22

X106	2010-02-23

X106	2010-02-24

X106	2010-02-25*/


请高手帮忙.感谢

...全文
145 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
allen750 2010-03-10
  • 打赏
  • 举报
 回复
太强了，终于看明了
select name,[date]
from
(select id = day([date])-(select count(1) from @table where name=t.name and [date]<=t.[date]),*
from @table t) r
where id in (select id from
(select id = day([date])-(select count(1) from @table where name=t.name and [date]<=t.[date]),*
from @table t)
h
group by id
having count(1) >= 21)
csdyyr 2010-03-10
  • 打赏
  • 举报
 回复 
--修改

select distinct t.* from @table as t

, (

select date as mindate  from @table as t

where exists(select 1 from @table as t2 where cast(t2.date as smalldatetime) between  cast(t.date as smalldatetime) and dateadd(day,20,cast(t.date as smalldatetime)) having count(*)=21)

) t2

where  cast(t.date as smalldatetime) between mindate and dateadd(day,21,cast(mindate as smalldatetime))
duanzhi1984 2010-03-10
  • 打赏
  • 举报
 回复
引用 13 楼 csdyyr 的回复:
引用 12 楼 duanzhi1984 的回复:引用 7 楼 csdyyr 的回复: SQL codeselect t.*from@tableas t , (selectmin(date)as mindatefrom@tableas twhereexists(select1from@tableas t2wherecast(t2.dateassmalldatetime)betweencast(t.dateassmalldatetime)anddateadd(day,20,cast(t?- 你的查询有点问题,查询中无 2010-02-25
最后一行:20->21

谢谢!!!!!!!!
非常感谢各位
csdyyr 2010-03-10
  • 打赏
  • 举报
 回复
引用 12 楼 duanzhi1984 的回复:
引用 7 楼 csdyyr 的回复:
SQL codeselect t.*from@tableas t
, (selectmin(date)as mindatefrom@tableas twhereexists(select1from@tableas t2wherecast(t2.dateassmalldatetime)betweencast(t.dateassmalldatetime)anddateadd(day,20,cast(t?-
你的查询有点问题,查询中无

2010-02-25

最后一行:20->21
duanzhi1984 2010-03-10
  • 打赏
  • 举报
 回复
引用 7 楼 csdyyr 的回复:
SQL codeselect t.*from@tableas t
, (selectmin(date)as mindatefrom@tableas twhereexists(select1from@tableas t2wherecast(t2.dateassmalldatetime)betweencast(t.dateassmalldatetime)anddateadd(day,20,cast(t?-

你的查询有点问题,查询中无

2010-02-25
水族杰纶 2010-03-10
  • 打赏
  • 举报
 回复 
declare @table table ([name] varchar(10),[date] varchar(10))



insert into @table

 select 'X106','2010-01-01' union all 

 select 'X106','2010-01-02' union all 

 select 'X106','2010-01-03' union all 

 select 'X106','2010-01-09' union all 

 select 'X106','2010-01-10' union all 

 select 'X106','2010-01-16' union all 

 select 'X106','2010-01-17' union all 

 select 'X106','2010-01-23' union all 

 select 'X106','2010-01-24' union all 

 select 'X106','2010-01-30' union all 

 select 'X106','2010-01-31' union all 

 select 'X106','2010-02-04' union all 

 select 'X106','2010-02-05' union all 

 select 'X106','2010-02-06' union all 

 select 'X106','2010-02-07' union all 

 select 'X106','2010-02-08' union all 

 select 'X106','2010-02-09' union all 

 select 'X106','2010-02-10' union all 

 select 'X106','2010-02-11' union all 

 select 'X106','2010-02-12' union all 

 select 'X106','2010-02-13' union all 

 select 'X106','2010-02-14' union all 

 select 'X106','2010-02-15' union all 

 select 'X106','2010-02-16' union all 

 select 'X106','2010-02-17' union all 

 select 'X106','2010-02-18' union all 

 select 'X106','2010-02-19' union all 

 select 'X106','2010-02-20' union all 

 select 'X106','2010-02-21' union all 

 select 'X106','2010-02-22' union all 

 select 'X106','2010-02-23' union all 

 select 'X106','2010-02-24' union all 

 select 'X106','2010-02-25' union all 

 select 'X106','2010-02-27' 

select [name],

       [date],

       ID=replace([date],'-',''),

       CNT=cast(replace([date],'-','')as bigint)-

       (select count(*) from @table n where cast(replace(m.[date],'-','')as bigint)

        >cast(replace(n.[date],'-','')as bigint))

into #    

from @table m  

select name,       

       date 

from # t 

where (select count(*) from # where cnt=t.cnt)>20

drop table #

/*

name       date

---------- ----------

X106       2010-02-04

X106       2010-02-05

X106       2010-02-06

X106       2010-02-07

X106       2010-02-08

X106       2010-02-09

X106       2010-02-10

X106       2010-02-11

X106       2010-02-12

X106       2010-02-13

X106       2010-02-14

X106       2010-02-15

X106       2010-02-16

X106       2010-02-17

X106       2010-02-18

X106       2010-02-19

X106       2010-02-20

X106       2010-02-21

X106       2010-02-22

X106       2010-02-23

X106       2010-02-24

X106       2010-02-25



(22 個資料列受到影響)



*/
sql_db 2010-03-10
  • 打赏
  • 举报
 回复 
select px=identity(int,1,1),* into #1 from @table t where not exists(select 1 from @table where datediff(dd,[date],t.[date])=1)

select px=identity(int,1,1),* into #2 from @table t where not exists(select 1 from @table where datediff(dd,[date],t.[date])=-1)

go



select a.* from # a,#1 b,#2 c where

c.px=b.px and

datediff(dd,b.[date],c.[date])>=21 and

a.[date] between b.[date] and c.[date]



/**

name       date       

---------- ---------- 

X106       2010-02-04

X106       2010-02-05

X106       2010-02-06

X106       2010-02-07

X106       2010-02-08

X106       2010-02-09

X106       2010-02-10

X106       2010-02-11

X106       2010-02-12

X106       2010-02-13

X106       2010-02-14

X106       2010-02-15

X106       2010-02-16

X106       2010-02-17

X106       2010-02-18

X106       2010-02-19

X106       2010-02-20

X106       2010-02-21

X106       2010-02-22

X106       2010-02-23

X106       2010-02-24

X106       2010-02-25



（所影响的行数为 22 行）



**/
nianran520 2010-03-10
  • 打赏
  • 举报
 回复 
declare @table table (name varchar(10),[date] varchar(10))



insert into @table

 select 'X106','2010-01-01' union all 

 select 'X106','2010-01-02' union all 

 select 'X106','2010-01-03' union all 

 select 'X106','2010-01-09' union all 

 select 'X106','2010-01-10' union all 

 select 'X106','2010-01-16' union all 

 select 'X106','2010-01-17' union all 

 select 'X106','2010-01-23' union all 

 select 'X106','2010-01-24' union all 

 select 'X106','2010-01-30' union all 

 select 'X106','2010-01-31' union all 

 select 'X106','2010-02-04' union all 

 select 'X106','2010-02-05' union all 

 select 'X106','2010-02-06' union all 

 select 'X106','2010-02-07' union all 

 select 'X106','2010-02-08' union all 

 select 'X106','2010-02-09' union all 

 select 'X106','2010-02-10' union all 

 select 'X106','2010-02-11' union all 

 select 'X106','2010-02-12' union all 

 select 'X106','2010-02-13' union all 

 select 'X106','2010-02-14' union all 

 select 'X106','2010-02-15' union all 

 select 'X106','2010-02-16' union all 

 select 'X106','2010-02-17' union all 

 select 'X106','2010-02-18' union all 

 select 'X106','2010-02-19' union all 

 select 'X106','2010-02-20' union all 

 select 'X106','2010-02-21' union all 

 select 'X106','2010-02-22' union all 

 select 'X106','2010-02-23' union all 

 select 'X106','2010-02-24' union all 

 select 'X106','2010-02-25' union all 

 select 'X106','2010-02-27' 



select name,[date]

from 

(select id = day([date])-(select count(1) from @table where name=t.name and [date]<=t.[date]),* 

from @table t) r

where id in (select id from

(select id = day([date])-(select count(1) from @table where name=t.name and [date]<=t.[date]),* 

from @table t) 

h

group by id

having count(1) >= 21)



name       date       

---------- ---------- 

X106       2010-02-04

X106       2010-02-05

X106       2010-02-06

X106       2010-02-07

X106       2010-02-08

X106       2010-02-09

X106       2010-02-10

X106       2010-02-11

X106       2010-02-12

X106       2010-02-13

X106       2010-02-14

X106       2010-02-15

X106       2010-02-16

X106       2010-02-17

X106       2010-02-18

X106       2010-02-19

X106       2010-02-20

X106       2010-02-21

X106       2010-02-22

X106       2010-02-23

X106       2010-02-24

X106       2010-02-25



（所影响的行数为 22 行）
duanzhi1984 2010-03-10
  • 打赏
  • 举报
 回复
我们的服务器是2000的,能用2000的搞一个吗?
csdyyr 2010-03-10
  • 打赏
  • 举报
 回复 
select t.* from @table as t

, (

select min(date) as mindate  from @table as t

where exists(select 1 from @table as t2 where cast(t2.date as smalldatetime) between  cast(t.date as smalldatetime) and dateadd(day,20,cast(t.date as smalldatetime)) having count(*)=21)

) t2

where  cast(t.date as smalldatetime) between mindate and dateadd(day,20,cast(mindate as smalldatetime))
-狙击手- 2010-03-10
  • 打赏
  • 举报
 回复
引用 4 楼 duanzhi1984 的回复:
引用 1 楼 wufeng4552 的回复:select 'X106','2010-01-01' union all select 'X106','2010-01-02' union all select 'X106','2010-01-03' union all 這也連續阿威什麼不算?
连续21天以上的记录。。
谢谢


;with cte
as
(select rid=row_number() over (order by getdate()),* from @table)
select name,[date]
from cte where cast([date] as int)- rid in (
select cast([date] as int)- rid as rid from cte group by cast([date] as int)- rid having count(1) >21)
-狙击手- 2010-03-10
  • 打赏
  • 举报
 回复
;with cte
as
(select rid=row_number() over (order by getdate()),* from @table)
select name,[date]
from cte where cast([date] as int)- rid in (
select cast([date] as int)- rid as rid from cte group by cast([date] as int)- rid having count(1) > 5)
duanzhi1984 2010-03-10
  • 打赏
  • 举报
 回复
引用 1 楼 wufeng4552 的回复:
select 'X106','2010-01-01' union all
select 'X106','2010-01-02' union all
select 'X106','2010-01-03' union all
這也連續阿
威什麼不算?

连续21天以上的记录。。
谢谢
duanzhi1984 2010-03-10
  • 打赏
  • 举报
 回复
哦,不好意思,

连续21天以上的记录。。
谢谢
sql_db 2010-03-10
  • 打赏
  • 举报
 回复
2-4到2-25不间断？
水族杰纶 2010-03-10
  • 打赏
  • 举报
 回复
select 'X106','2010-01-01' union all
select 'X106','2010-01-02' union all
select 'X106','2010-01-03' union all
這也連續阿
威什麼不算?
【南卡樱桃|读书笔记《学习高手》】
∝学霸分享 6大课 通过英语、写作和SMART原则、OKR工作法、LEO解读五步法等文章。同样适用于国内教育环境。 ∝3 学霸分享，直通世界名校的超级学习法 ∞第18课 LEO手把手教你如何学好英语 ◆第1小课 单词 ◆第2小课 语法 ◆第3小课 听力 ◆第4小课 阅读 ◆第5小课 口语 LEO的学习仪式感 我在不同英语学习阶段使用的教材 ∞第19课 三要素写作法，把文章写到读者心里去 ◆第1小课 Ethos，可信 ◆第2小课 Pathos，情感 ◆第3小课 Logos，逻辑 ◆19-本课核心方法回顾 ∞第
经典箴言和人生感悟
人的一生中只有七次机会，平均每七年拥有一次，大概在25岁到75岁，第一次通常抓不到，因为太年轻，最后一次也抓不到，因为太老。中途还有2次因为自己错过，所以抓不到。所以对于人来说人真正才会有三次机会，人的一生真正只有三次，只有称为能改变自己命运的机会才可以称作是机会。其实人生的机会很快就会过去。能真正抓住机会的人，首先靠的是自己个人的能力；其次靠的是环境。 一个人如果素质不好，行为不好，那么即使你
DOS基础命令汇总（一）
DOS基础命令汇总（一） 单击“开始”--“运行”--输入“cmd”回车即可(也可以按 WIN+R组合键打开运行输入框) 有些命令因为参数过多，小编也不可能全不都拿出来给大家讲。只能给大家介绍一些比较常用或实用的命令 如果有高手路过, 也请帮忙指正一下, 避免误导勤奋的好朋友们. 同时建议有心想一起玩的条友多动手敲敲键盘, 小编数年来的经验证实了, 学IT, 不动手的话, 也
毕业两年后开贴！给应届毕业的DDMM们的建议（一）
第1节 日期：2009-7-2020:22:20 首先希望大家不要吝啬，看了之后留下点只言片语也好，让我能将这个贴坚持坚持下来。 其次，贴里的观点全部来自我个人的生活观察和经历总结而形成，难免有失偏颇，因此欢迎有不同意见的朋友辩论，但请对事勿对人，注意分寸，不要过激，相互尊重一下，感谢！ 一、大学生在步入社会之前必须具有政治素质和政治觉...
html seo
HTML 标签与Nofollow介绍 HTML  标签  元素可提供有关页面的元信息（meta-information），比如针对搜索引擎和更新频度的描述和关键词。  标签位于文档的头部，不包含任何内容。 标签的属性定义了与文档相关联的名称/值对。 在网页的HTML源代码中一个重要的代码“”（即通常所说的META标签）。META标签用来描述一个HTML网页文档的属性，例如作者、日期和时间、
疑难问题

22,207

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章