求一条SQL语句

[ai7821]

a b c d e
145 120121 2012-01-17 00:00:00.000 2010 1
145 120220 2012-03-03 00:00:00.000 2520 1
145 120220 2012-03-03 00:00:00.000 2783 1
145 120220 2012-03-30 00:00:00.000 3056 1
146 120206 2012-02-08 00:00:00.000 2159 1
147 120207 2012-02-08 00:00:00.000 2159 2
149 111201 2011-10-25 00:00:00.000 1908 1
149 120226 2012-03-01 00:00:00.000 2491 2
允许a b 字段重复， 查询才出当 a 、 b重复时候 c字段最大的结果当a、b、c 相同时候d 最大值
查询出结果如下
a b c d e
145 120121 2012-01-17 00:00:00.000 2010 1
145 120220 2012-03-03 00:00:00.000 2783 1
145 120220 2012-03-30 00:00:00.000 3056 1
146 120206 2012-02-08 00:00:00.000 2159 1
147 120207 2012-02-08 00:00:00.000 2159 2
149 111201 2011-10-25 00:00:00.000 1908 1
149 120226 2012-03-01 00:00:00.000 2491 2

[开着拖拉机泡妞]

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([a] int,[b] int,[c] datetime,[d] int,[e] int)
insert [test]
select 145,120121,'2012-01-17 00:00:00.000',2010,1 union all
select 145,120220,'2012-03-03 00:00:00.000',2520,1 union all
select 145,120220,'2012-03-03 00:00:00.000',2783,1 union all
select 145,120220,'2012-03-30 00:00:00.000',3056,1 union all
select 146,120206,'2012-02-08 00:00:00.000',2159,1 union all
select 147,120207,'2012-02-08 00:00:00.000',2159,2 union all
select 149,111201,'2011-10-25 00:00:00.000',1908,1 union all
select 149,120226,'2012-03-01 00:00:00.000',2491,2

select * from [test] a 
where d=(select MAX(d) from test b where a.a=b.a and a.b=b.b and a.c=b.c)
/*
a	b	c	d	e
145	120121	2012-01-17 00:00:00.000	2010	1
145	120220	2012-03-03 00:00:00.000	2783	1
145	120220	2012-03-30 00:00:00.000	3056	1
146	120206	2012-02-08 00:00:00.000	2159	1
147	120207	2012-02-08 00:00:00.000	2159	2
149	111201	2011-10-25 00:00:00.000	1908	1
149	120226	2012-03-01 00:00:00.000	2491	2
*/

[知所行止的日毕一省]

 select a.* 
   from a,
(select a,b,max(c) cmax from tb group by a,b) b
  where a.a=b.a and a.b=b.b and a.c=c.cmax

  Union All

 select a.* from a,
(select a,b,c,max(d) dmax from tb group by a,b,c) b
  where a.a=b.a and a.b=b.b and a.c=c.c and a.d=b.dmax

[yushen945]

顶下2楼的结果，正解`!~!~

[ai7821]

谢谢，虽然里面有很多错误，但是语法是对的
select a.*
from a,
(select a,b,max(c) cmax from a group by a,b) b
where a.a=b.a and a.b=b.b and a.c=b.cmax

Union All

select a.* from a,
(select a,b,c,max(d) dmax from a group by a,b,c) b
where a.a=b.a and a.b=b.b and a.c=b.c and a.d=b.dmax