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
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,2select * 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
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