表如下
Id name ReceiveDate
1 张三 2012-02-21
1 张三 2012-02-23
1 张三 2012-02-24
2 张四 2012-02-24
2 张四 NULL
3 李五
结果
ID name 开始时间 结束时间
1 张三 2012-02-21 2012-02-24
2 张四 2012-02-24
3 李五
这里有按ID,和 NAME 分,
最小时间: 取ReceiveDate,的最小时间。
最大时间: 有几条数据,ReceiveDate 都有值了,就取最大时间为结束时间,只要有一个没填结束时间就为空
Id name ReceiveDate
1 张三 2012-02-21
1 张三 2012-02-23
1 张三 2012-02-24
2 张四 2012-02-24
2 张四 NULL
3 李五
结果
ID name 开始时间 结束时间
1 张三 2012-02-21 2012-02-24
2 张四 2012-02-24
3 李五
这里有按ID,和 NAME 分,
最小时间: 取ReceiveDate,的最小时间。
最大时间: 有几条数据,ReceiveDate 都有值了,就取最大时间为结束时间,只要有一个没填结束时间就为空
select
a.id,a.name,a.开始时间,b.结束时间
from
(
select
id,name,
max(case px when 1 then ReceiveDate else '' end) as 开始时间
from
(select px=row_number()over(partition by id,name order by ReceiveDate)t
group by
id,name
)a
left join
(
select
id,name,
max(case px when 1 then ReceiveDate else '' end) as 开始时间
from
(select px=row_number()over(partition by id,name order by ReceiveDate desc)t
group by
id,name
)b
on
a.id=b.id
and
a.name=b.name
select id,name,min(date) mindate,
(case when max(date)=min(date) then null else max(date) end) maxdate
from tb
group by id,name--try
go
create table [tb]([Id] int,[name] varchar(4),[ReceiveDate] datetime)
insert [tb]
select 1,'张三','2012-02-21' union all
select 1,'张三','2012-02-23' union all
select 1,'张三','2012-02-24' union all
select 2,'张四','2012-02-24' union all
select 2,'张四',null union all
select 3,'李五',null
goselect id,name,dt1 as 开始时间,case when dt1=dt2 then null else dt2 end as 结束时间
from(
select id,name,min(receivedate) dt1,max(receivedate) dt2
from tb
group by id,name
) t
order by id/**
id name 开始时间 结束时间
----------- ---- ----------------------- -----------------------
1 张三 2012-02-21 00:00:00.000 2012-02-24 00:00:00.000
2 张四 2012-02-24 00:00:00.000 NULL
3 李五 NULL NULL(3 行受影响)
**/
declare @t table
(Id int,name varchar(4),ReceiveDate datetime)
insert into @t
select 1,'张三','2012-02-21' union all
select 1,'张三','2012-02-23' union all
select 1,'张三','2012-02-24' union all
select 2,'张四','2012-02-24' union all
select 2,'张四',null union all
select 3,'李五',nullselect
Id,name,min(ReceiveDate) as 开始时间,
case when (select count(*) from @t
where Id=t.Id and name=t.name and ReceiveDate is null)=0 then max(ReceiveDate) else null end as 结束时间
from @t t group by Id,name order by id
/*
Id name 开始时间 结束时间
----------- ---- ----------------------- -----------------------
1 张三 2012-02-21 00:00:00.000 2012-02-24 00:00:00.000
2 张四 2012-02-24 00:00:00.000 NULL
3 李五 NULL NULL
*/
表如下
Id name ReceiveDate
1 张三 2012-02-21
1 张三 2012-02-23
1 张三 2012-02-24
2 张四 2012-02-24
2 张四 NULL
3 李五
结果
ID name 开始时间 结束时间
1 张三 2012-02-21 2012-02-24
2 张四 2012-02-24
3 李五
这里有按ID,和 NAME 分,
最小时间: 取ReceiveDate,的最小时间。
最大时间: 有几条数据,ReceiveDate 都有值了,就取最大时间为结束时间,只要有一个没填结束时间就为空
*/go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
Id int,
name varchar(10),
ReceiveDate date
)
go
insert tbl
select 1,'张三','2012-02-21' union all
select 1,'张三','2012-02-23' union all
select 1,'张三','2012-02-24' union all
select 2,'张四','2012-02-24' union all
select 2,'张四',null union all
select 3,'李五',null
;with T
as
(select Id,name,
isnull(min(ReceiveDate),null) as 开始时间,
isnull(max(ReceiveDate),null) as 结束时间
from tbl
group by Id,name
)
select Id,name,开始时间,
case when 开始时间=结束时间 then null else 结束时间 end as 结束时间
from T
order by Id asc/*
Id name 开始时间 结束时间
1 张三 2012-02-21 2012-02-24
2 张四 2012-02-24 NULL
3 李五 NULL NULL
*/--来个简单点的
SELECT ID,Name,
min(receivedate) as '开始时间',
case when count(*)=count(receivedate) and count(*)=1 then NULL
when count(*)=count(receivedate) and count(*)>1 then max(receivedate)
when count(*)<>count(receivedate) then NULL end as '结束时间'
FROM TB
group by ID,name
order by id
第九行, (select px=row_number()over(partition by id,name order by ReceiveDate)t 是不是有问题啊
USE tempdbcreate table mytest
(
id int,
[name] nvarchar(10),
recevidate datetime
)insert into mytest(id,[name],recevidate) values ('1','张三','2012-02-21')
insert into mytest(id,[name],recevidate) values ('1','张三','2012-02-23')
insert into mytest(id,[name],recevidate) values ('1','张三','2012-02-24')
insert into mytest(id,[name],recevidate) values ('2','张四','2012-02-24')
insert into mytest(id,[name],recevidate) values ('2','张四',null)
insert into mytest(id,[name],recevidate) values ('3','李武',null)SELECT * FROM mytestSELECT id,[name],min(recevidate) as beginTime,
case
when max(recevidate) = min(recevidate) then null
else max(recevidate)
end as endTime FROM mytest
group by id,[name] order by id asc,[name] desc
1 张三 2012-02-21 00:00:00.000 2012-02-24 00:00:00.000
2 张四 2012-02-24 00:00:00.000 NULL
3 李武 NULL NULL
学习,方法最简单。
create table #TT([Id] int,[name] varchar(4),[ReceiveDate] datetime)
insert #TT
select 1,'张三','2012-02-21' union all
select 1,'张三','2012-02-23' union all
select 1,'张三','2012-02-24' union all
select 1,'张三','2012-02-25' union all
select 2,'张四','2012-02-24' union all
select 2,'张四','2012-02-28' union all
select 2,'张四',null union all
select 3,'李五',nullselect id,name,min(ReceiveDate) mindate,
(case when max(ReceiveDate)=min(ReceiveDate) then null else max(ReceiveDate) end) maxdate
from #TT
group by id,name
drop table #TT