select * from A where exists(select c from A t where t.c=dateadd(day,-1,A.c) and t.c<A.c)
楼上的貌似也不对,,, 我贴个测试建表的代码 方便大神们测试create table A ( id int, c datetime )insert into A values(1,'2011-3-16') insert into A values(2,'2011-3-15') insert into A values(3,'2011-3-13') insert into A values(4,'2011-3-12') insert into A values(5,'2011-3-11') insert into A values(6,'2011-3-10') insert into A values(7,'2011-3-9') insert into A values(8,'2011-3-7') insert into A values(9,'2011-3-6')select * from Ago drop table A
不知道是不是这样的 ---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-03-16 10:59:19 -- Verstion: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] int,[c] datetime) insert [tb] select 1,'2011-3-16' union all select 2,'2011-3-15' union all select 3,'2011-3-13' union all select 4,'2011-3-12' union all select 5,'2011-3-11' union all select 6,'2011-3-10' union all select 7,'2011-3-8' union all select 8,'2011-3-7' --------------开始查询-------------------------- select b.* from (select row_number()over(order by getdate()) as id0,* from tb) a, (select row_number()over(order by getdate()) as id0,* from tb) b where datediff(dd,a.c,b.c)>0 and a.id0-1=b.id0 ----------------结果---------------------------- /* id0 ID c -------------------- ----------- ----------------------- 1 1 2011-03-16 00:00:00.000 2 2 2011-03-15 00:00:00.000 3 3 2011-03-13 00:00:00.000 4 4 2011-03-12 00:00:00.000 5 5 2011-03-11 00:00:00.000 6 6 2011-03-10 00:00:00.000 7 7 2011-03-08 00:00:00.000(7 行受影响) */
是不是这样:with cte as ( select dt=c-(row_number() over(order by c)),* from a ) select 开始日期=min(c),结束日期=max(c),连续天数=count(*) from cte group by dt/* 开始日期 结束日期 连续天数 ----------------------- ----------------------- ----------- 2011-03-06 00:00:00.000 2011-03-07 00:00:00.000 2 2011-03-09 00:00:00.000 2011-03-13 00:00:00.000 5 2011-03-15 00:00:00.000 2011-03-16 00:00:00.000 2(3 行受影响) */
;with m as (select *,ROW_NUMBER()over( order by c)as px from A) ,mm as (select c,ROW_NUMBER() over(partition by (DATEadd(DD,-px,c)) order by id) as cc from m ) select * from mm /* c cc 2011-03-07 00:00:00.000 1 2011-03-06 00:00:00.000 2 2011-03-13 00:00:00.000 1 2011-03-12 00:00:00.000 2 2011-03-11 00:00:00.000 3 2011-03-10 00:00:00.000 4 2011-03-09 00:00:00.000 5 2011-03-16 00:00:00.000 1 2011-03-15 00:00:00.000 2 */
貌似将楼主的意思理解错了:;with cte as ( select dt=c-(row_number() over(order by c)),* from a ) select id,c from cte where dt = (select top (1) dt from cte group by dt order by count(*) desc) /* id c ----------- ----------------------- 7 2011-03-09 00:00:00.000 6 2011-03-10 00:00:00.000 5 2011-03-11 00:00:00.000 4 2011-03-12 00:00:00.000 3 2011-03-13 00:00:00.000(5 行受影响) */;with cte as ( select dt=c-(row_number() over(order by c)),* from a ) select top (1) 开始日期=min(c),结束日期=max(c),连续天数=count(*) from cte group by dt order by count(*) desc /* 开始日期 结束日期 连续天数 ----------------------- ----------------------- ----------- 2011-03-09 00:00:00.000 2011-03-13 00:00:00.000 5(1 行受影响) */ 如OK,楼主要记得结贴给分啊
where exists(select c from A t where t.c=dateadd(day,-1,A.c) and t.c<A.c)
我贴个测试建表的代码 方便大神们测试create table A
(
id int,
c datetime
)insert into A values(1,'2011-3-16')
insert into A values(2,'2011-3-15')
insert into A values(3,'2011-3-13')
insert into A values(4,'2011-3-12')
insert into A values(5,'2011-3-11')
insert into A values(6,'2011-3-10')
insert into A values(7,'2011-3-9')
insert into A values(8,'2011-3-7')
insert into A values(9,'2011-3-6')select * from Ago
drop table A
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-16 10:59:19
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[c] datetime)
insert [tb]
select 1,'2011-3-16' union all
select 2,'2011-3-15' union all
select 3,'2011-3-13' union all
select 4,'2011-3-12' union all
select 5,'2011-3-11' union all
select 6,'2011-3-10' union all
select 7,'2011-3-8' union all
select 8,'2011-3-7'
--------------开始查询--------------------------
select
b.*
from
(select row_number()over(order by getdate()) as id0,* from tb) a,
(select row_number()over(order by getdate()) as id0,* from tb) b
where
datediff(dd,a.c,b.c)>0 and a.id0-1=b.id0
----------------结果----------------------------
/* id0 ID c
-------------------- ----------- -----------------------
1 1 2011-03-16 00:00:00.000
2 2 2011-03-15 00:00:00.000
3 3 2011-03-13 00:00:00.000
4 4 2011-03-12 00:00:00.000
5 5 2011-03-11 00:00:00.000
6 6 2011-03-10 00:00:00.000
7 7 2011-03-08 00:00:00.000(7 行受影响)
*/
(
select dt=c-(row_number() over(order by c)),* from a
)
select 开始日期=min(c),结束日期=max(c),连续天数=count(*) from cte group by dt/*
开始日期 结束日期 连续天数
----------------------- ----------------------- -----------
2011-03-06 00:00:00.000 2011-03-07 00:00:00.000 2
2011-03-09 00:00:00.000 2011-03-13 00:00:00.000 5
2011-03-15 00:00:00.000 2011-03-16 00:00:00.000 2(3 行受影响)
*/
,mm as (select c,ROW_NUMBER() over(partition by (DATEadd(DD,-px,c)) order by id) as cc from m )
select * from mm /*
c cc
2011-03-07 00:00:00.000 1
2011-03-06 00:00:00.000 2
2011-03-13 00:00:00.000 1
2011-03-12 00:00:00.000 2
2011-03-11 00:00:00.000 3
2011-03-10 00:00:00.000 4
2011-03-09 00:00:00.000 5
2011-03-16 00:00:00.000 1
2011-03-15 00:00:00.000 2
*/
(
select dt=c-(row_number() over(order by c)),* from a
)
select id,c from cte where dt = (select top (1) dt from cte group by dt order by count(*) desc)
/*
id c
----------- -----------------------
7 2011-03-09 00:00:00.000
6 2011-03-10 00:00:00.000
5 2011-03-11 00:00:00.000
4 2011-03-12 00:00:00.000
3 2011-03-13 00:00:00.000(5 行受影响)
*/;with cte as
(
select dt=c-(row_number() over(order by c)),* from a
)
select top (1) 开始日期=min(c),结束日期=max(c),连续天数=count(*) from cte group by dt order by count(*) desc
/*
开始日期 结束日期 连续天数
----------------------- ----------------------- -----------
2011-03-09 00:00:00.000 2011-03-13 00:00:00.000 5(1 行受影响)
*/
如OK,楼主要记得结贴给分啊
#13 是最符合题意的,,可是。没有用MS SQL,我再等等,就结贴吧。。如果没 MSSQL 的。。