select a.*,b.f1
from A,(select a.f1,
(select top 1 k from b where v=a.v1) as k1,
(select top 1 k from b where v=a.v2) as k2
from c ) b
where a.k1=b.k1 and a.k2=b.k2
from A,(select a.f1,
(select top 1 k from b where v=a.v1) as k1,
(select top 1 k from b where v=a.v2) as k2
from c ) b
where a.k1=b.k1 and a.k2=b.k2
from A,(select f1,
(select top 1 k from b where v=a.v1) as k1,
(select top 1 k from b where v=a.v2) as k2
from c ) b
where a.k1=b.k1 and a.k2=b.k2
from 表A a
,表B b1,表B b2
,表C c
where a.f2>10
and a.k1=b1.k and a.k2=b2.k
and b1.v=c.v1 and b2.v=c.v2
from 表A a
,表B b1,表B b2
,表C c
where a.f2>10
and a.k1=b1.k and a.k2=b2.k
and b1.v=c.v1 and b2.v=c.v2
from A,(select f1,
(select top 1 k from b where v=c.v1) as k1,
(select top 1 k from b where v=c.v2) as k2
from c ) b
where a.k1=b.k1 and a.k2=b.k2
create table 表A(f1 int,k1 varchar(10),k2 varchar(10),f2 int)
insert 表A select 10,'1','2',100create table 表B(k varchar(10),v varchar(10))
insert 表B select '1','5'
union all select '2','0'create table 表C(f1 int,v1 varchar(10),v2 varchar(10))
insert 表C select 23,'5','0'
union all select 24,'5','1'
go--查询
select a.*,c.f1 --指定要查询出的字段名
from 表A a
,表B b1,表B b2
,表C c
where a.f2>10
and a.k1=b1.k and a.k2=b2.k
and b1.v=c.v1 and b2.v=c.v2
go--删除测试
drop table 表A,表B,表C/*--测试结果f1 k1 k2 f2 f1
----------- ---------- ---------- ----------- -----------
10 1 2 100 23(所影响的行数为 1 行)
--*/
A.f1,
A.k1,
A.k2,
A.f2,
C.f1
from
A ,
B B1,
B B2,
C
where
A.k1 = B1.k
and
A.k2 = B2.k
and
C.v1 = B1.v
and
C.v2 = B2.v
and
A.f2 > 10
union all select 'c','d'
--测试数据
create table 表A(f1 int,k1 varchar(10),k2 varchar(10),f2 int)
insert 表A select 10,'1','2',100create table 表B(k varchar(10),v varchar(10))
insert 表B select '1','5'
union all select '2','0'。
。
通过了