日期:2014-05-17 浏览次数:20559 次
if object_id('[Tb_A]') is not null drop table [Tb_A]
go
create table [Tb_A]([A] varchar(1),[B] int,[C] varchar(1),[D] varchar(1),[f] datetime)
insert [Tb_A]
select 'a',1,'w','b','2013-05-10' union all
select 'a',2,'x','y','2013-06-10' union all
select 'a',3,'z','t','2013-07-10' union all
select 'b',2,'g','t','2013-05-10' union all
select 'b',1,'m','h','2013-05-13' union all
select 'b',5,'u','q','2013-06-10'
go
select a,SUM(b) as b,
max(case when rownum = 1 then c else null end) as c,
MAX(case when rownum = a_count then d else null end) as d
from
(
select *,
ROW_NUMBER() over(partition by a order by f) as rownum,
count(*) over(partition by a) as a_count
from Tb_A
)t
where rownum in (1,a_count)
group by a
/*
a b c d
a 4 w t
b 7 g q
*/