日期:2014-05-18  浏览次数:20506 次

求一SQL 写法,有点难。
有一表如下
F1                     F2                                   F3           F4                     F5
5501 20070300017 20070308 5151 20070100441
5501 20070300021 20070309 5151 20070100441
5501 20070300023 20070312 5151 20070100441
5502 20070200112 20070209 5151 20070200084
5502 20070300032 20070321 5151 20070200084
5502 20070300205 20070321 5151 20070200084
预求得同一F5值中F3为最大值   的F1与F2   值,如果F3最大有重得的,就取F2为最大值的SQL   写法。
想得到的结果如下:
5501 20070300023 20070312 5151 20070100441
5502 20070300032 20070321 5151 20070200084

------解决方案--------------------
--多谢谢楼上兄台提醒,修改如下:

if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(F1 varchar(20),F2 varchar(20),F3 varchar(20),F4 varchar(20),F5 varchar(20))
insert into tb(F1,F2,F3,F4,F5) values( '5501 ', '20070300017 ', '20070308 ', '5151 ', '20070100441 ')
insert into tb(F1,F2,F3,F4,F5) values( '5501 ', '20070300021 ', '20070309 ', '5151 ', '20070100441 ')
insert into tb(F1,F2,F3,F4,F5) values( '5501 ', '20070300023 ', '20070312 ', '5151 ', '20070100441 ')
insert into tb(F1,F2,F3,F4,F5) values( '5501 ', '20070300045 ', '20070309 ', '5151 ', '20070100441 ')
insert into tb(F1,F2,F3,F4,F5) values( '5502 ', '20070200112 ', '20070209 ', '5151 ', '20070200084 ')
insert into tb(F1,F2,F3,F4,F5) values( '5502 ', '20070300032 ', '20070321 ', '5151 ', '20070200084 ')
insert into tb(F1,F2,F3,F4,F5) values( '5502 ', '20070300205 ', '20070321 ', '5151 ', '20070200084 ')
go

select m.* from
(
select a.* from tb a,
(select f5,max(f3) f3 from tb group by f5) b
where a.f5 = b.f5 and a.f3 = b.f3
) m,
(
select f5 , max(f2) f2 from
(
select a.* from tb a,
(select f5,max(f3) f3 from tb group by f5) b
where a.f5 = b.f5 and a.f3 = b.f3
) n group by f5
) t
where m.f5 = t.f5 and m.f2 = t.f2

drop table tb

/*
F1 F2 F3 F4 F5
---- -------------------- -------------------- -------------------- -----------
5501 20070300023 20070312 5151 20070100441
5502 20070300205 20070321 5151 20070200084

(所影响的行数为 2 行)
*/

------解决方案--------------------
马可,能讲述一下,执行的顺序吗?
-----------------------------------
选中要执行的语句,按 "Ctrl + L "