高分求一个sql文,急急急急急

[xiaoyawww]

有三个表，关系很简单
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

[jacobsan]

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)


--
1 2 4 1
2 2 4 1

[xiaoyawww]

to 楼上大侠 libin_ftsafe(子陌红尘：当libin告别ftsafe)
谢谢您的回帖
现在实际上table1是一个很大的查询，能不能table1只查询1次，1次是最理想的状态，多谢了。

[xiaoyawww]

to楼上美女 jacobsan(梅)
谢谢你的回帖
现在table1是一个很大的子查询，能不能让sql文再简单一点，具体就是table1尽量少在子查询使用。
谢谢了。

[leihentulong]

mark.

[mengmou]

美女的写法应该最简单了吧。

[xiaoyawww]

先抬一下这个帖子，如果没有更适合我的答案，我就准备结贴了，稍微等待中。。。。。