declare @t table(name varchar(8),date datetime)
insert into @t select
'张三', '2005-01-10' union all select
'张三', '2005-01-11' union all select
'李四', '2005-01-11' union all select
'张三', '2005-01-12' union all select
'李四', '2005-01-12' union all select
'张三', '2005-02-09' union all select
'张三', '2005-02-10'
select name,min(date),max(date) from @t group by name,convert(varchar(7),date,120)
insert into @t select
'张三', '2005-01-10' union all select
'张三', '2005-01-11' union all select
'李四', '2005-01-11' union all select
'张三', '2005-01-12' union all select
'李四', '2005-01-12' union all select
'张三', '2005-02-09' union all select
'张三', '2005-02-10'
select name,min(date),max(date) from @t group by name,convert(varchar(7),date,120)
name nvarchar(20),
date datetime
)
insert @原有记录 select
N'张三', '2005-01-10'
union all select
N'张三', '2005-01-11'
union all select
N'李四', '2005-01-11'
union all select
N'张三', '2005-01-12'
union all select
N'李四', '2005-01-12'
union all select
N'张三', '2005-02-09'
union all select
N'张三', '2005-02-10'select a.name,a.date,b.date
from @原有记录 a,@原有记录 b
where a.name=b.name
and a.date<b.date
and not exists (
select 1 from @原有记录
where name=a.name
and date=dateadd(day,-1,a.date)
)
and not exists (
select 1 from @原有记录
where name=b.name
and date=dateadd(day,1,b.date)
)
and not exists (
select 1 from @原有记录 x
where name=a.name
and date>a.date
and date<b.date
and not exists (
select 1 from @原有记录
where name=a.name
and date=dateadd(day,-1,x.date)
)
)--结果
name date date
-------------------- ------------------------------------------------------ ------------------------------------------------------
张三 2005-01-10 00:00:00.000 2005-01-12 00:00:00.000
李四 2005-01-11 00:00:00.000 2005-01-12 00:00:00.000
张三 2005-02-09 00:00:00.000 2005-02-10 00:00:00.000(所影响的行数为 3 行)
order by a.name,a.date
insert into @t select
'张三', '2005-01-10' union all select
'张三', '2005-01-11' union all select
'李四', '2005-01-11' union all select
'张三', '2005-01-12' union all select
'李四', '2005-01-12' union all select
'张三', '2005-02-09' union all select
'张三', '2005-02-10'
select name,min(date),max(date) from @t group by name,convert(varchar(7),date,120)
order by name desc,convert(varchar(7),date,120)
--结果(所影响的行数为 7 行)name 本月开始 本月截止
-------- ------------------------------------------------------ ------------------------------------------------------
张三 2005-01-10 00:00:00.000 2005-01-12 00:00:00.000
张三 2005-02-09 00:00:00.000 2005-02-10 00:00:00.000
李四 2005-01-11 00:00:00.000 2005-01-12 00:00:00.000(所影响的行数为 3 行)
select na,min(da),max(da) from test group by na desc ,MONTH(da);
+------+---------------------+---------------------+
| na | min(da) | max(da) |
+------+---------------------+---------------------+
| 张三 | 2005-01-10 00:00:00 | 2005-01-12 00:00:00 |
| 张三 | 2005-02-09 00:00:00 | 2005-02-10 00:00:00 |
| 李四 | 2005-01-11 00:00:00 | 2005-01-12 00:00:00 |
+------+---------------------+---------------------+
以上在 mysql 中运行
抱歉
create table test (na char(12),da datetime);
go
insert into test values ('张三','2005-01-09')
insert into test values ('张三','2005-01-11')
insert into test values ('张三','2005-01-12')
insert into test values ('张三','2005-02-09')
insert into test values ('张三','2005-02-10')
insert into test values ('李四','2005-01-11')
insert into test values ('李四','2005-01-12')
select na,min(da),max(da) from test group by na ,MONTH(da) order by na desc
====================================
张三 2005-01-09 00:00:00.000 2005-01-12 00:00:00.000
张三 2005-02-09 00:00:00.000 2005-02-10 00:00:00.000
李四 2005-01-11 00:00:00.000 2005-01-12 00:00:00.000以上 mssql中运行