sql server 分组问题
如:table Tb_A
A B C D f
a 1 w b 2013-05-10
a 2 x y 2013-06-10
a 3 z t 2013-07-10
b 2 g t 2013-05-10
b 1 m h 2013-05-13
b 5 u q 2013-06-10
查询结果
A B C D
a 6 w(第一个) t(最后一个)
b 8 g q
以f字段排序升序，大侠sql语句怎么写啊
[解决办法]
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
/*
abcd
a4wt
b7gq
*/