高分求一个sql文,急急急急急
有三个表，关系很简单
TABLE1:
field1 field2
1 1
1 2
1 3
2 1
2 2
2 3

TABLE2:
field2 field3
1 5
2 4
3 6

TABLE3:
field3 field4
5 1
4 1
6 2
联合起来的结果就是
field1 field2 field3 field4
1 1 5 1
1 2 4 1
1 3 6 2
2 1 5 1
2 2 4 1
2 3 6 2

对于每种field1 我要得到相对应的field4最小的那条
如果最小的有两条，我就要其中field3最小的那条，就是
1 2 4 1
2 2 4 1

叩谢。

[解决办法]
create table TABLE1(field1 int,field2 int)
insert into table1 select 1,1
insert into table1 select 1,2
insert into table1 select 1,3
insert into table1 select 2,1
insert into table1 select 2,2
insert into table1 select 2,3

create table TABLE2(field2 int,field3 int)
insert into table2 select 1,5
insert into table2 select 2,4
insert into table2 select 3,6

create table TABLE3(field3 int,field4 int)
insert into table3 select 5,1
insert into table3 select 4,1
insert into table3 select 6,2
go

select
a.field1,a.field2,b.field3,c.field4
from
TABLE1 a,TABLE2 b,TABLE3 c
where
a.field2=b.field2 and b.field3=c.field3
and
rtrim(b.field3)+ '- '+rtrim(c.field4)=(select
top 1 rtrim(e.field3)+ '- '+rtrim(f.field4)
from
table1 d,table2 e,table3 f
where
d.field2=e.field2 and e.field3=f.field3 and d.field1=a.field1
order by
f.field4,e.field3)
go

/*
field1 field2 field3 field4
----------- ----------- ----------- -----------
1 2 4 1
2 2 4 1
*/

drop table table1,table2,table3

go
[解决办法]
create table TABLE1(field1 int, field2 int)
insert into TABLE1 select 1 , 1 union all
select 1 , 2 union all
select 1 , 3 union all
select 2 , 1 union all
select 2 , 2 union all
select 2 , 3
create table TABLE2(field2 int, field3 int)
insert into TABLE2 select 1 , 5 union all
select 2 , 4 union all
select 3 , 6
create table TABLE3(field3 int, field4 int)

insert into TABLE3 select 5 , 1 union all
select 4 , 1 union all
select 6 , 2


select field1,table2.field2,table3.field3, field4 into # from TABLE1,TABLE2,TABLE3 where TABLE1.field2=TABLE2.field2 and TABLE2.field3=TABLE3.field3

select * from # t where not exists(select 1 from # where ((field3=t.field3 and field4 <t.field4) or field3 <t.field3) and field1=t.field1)


--
1241
2241

[解决办法]
mark.
[解决办法]
美女的写法应该最简单了吧。