有表A、B、C
表A字段
ID zd1
1 56
2 43
3 65
4 23
5 67
表B字段
AID zd2
3 78
4 33
表C字段
AID zd3
2 90
3 87
5 67
求一SQL语句,条件:A.ID<=4; B.AID为A.ID; C.AID为A.ID 并且表B和C中没有AID的字段显示为空。
根据条件结果要为:
ID zd1 zd2 zd3
1 56
2 43 90
3 65 78 87
4 23 33 不用外关联(JOIN)只用内关联。
表A字段
ID zd1
1 56
2 43
3 65
4 23
5 67
表B字段
AID zd2
3 78
4 33
表C字段
AID zd3
2 90
3 87
5 67
求一SQL语句,条件:A.ID<=4; B.AID为A.ID; C.AID为A.ID 并且表B和C中没有AID的字段显示为空。
根据条件结果要为:
ID zd1 zd2 zd3
1 56
2 43 90
3 65 78 87
4 23 33 不用外关联(JOIN)只用内关联。
select A.ID,
A.Zd1,
(select Zd2 from B where A.iD=Aid) as Zd2,
(select Zd3 from c where A.iD=Aid) as Zd3
from A
where ID<=4
insert into A values(1, 56)
insert into A values(2, 43)
insert into A values(3, 65)
insert into A values(4, 23)
insert into A values(5, 67)
create table B(AID int, zd2 int)
insert into B values(3, 78)
insert into B values(4, 33)
create table C(AID int, zd3 int)
insert into C values(2, 90 )
insert into C values(3, 87 )
insert into C values(5, 67 )
goselect A.* , B.zd2 , C.zd3 from A left join B on A.id = B.aid left join C on A.id = C.aid where a.id <= 4drop table A,B,C/*
ID zd1 zd2 zd3
----------- ----------- ----------- -----------
1 56 NULL NULL
2 43 NULL 90
3 65 78 87
4 23 33 NULL(4 行受影响)
*/
select ID,zd1,
zd2 = isnull(select zd2 from tableb where a.id = aid),''),
zd3 = isnull(select zd3 from tablec where a.id = aid),'')
from tablea a
zd2 = isnull(select zd2 from tableb where a.id = aid),''),
zd3 = isnull(select zd3 from tablec where a.id = aid),'')
from tablea a
where id <=4
insert into A values(1, 56)
insert into A values(2, 43)
insert into A values(3, 65)
insert into A values(4, 23)
insert into A values(5, 67)
create table B(AID int, zd2 int)
insert into B values(3, 78)
insert into B values(4, 33)
create table C(AID int, zd3 int)
insert into C values(2, 90 )
insert into C values(3, 87 )
insert into C values(5, 67 )
goselect A.* , isnull(cast(B.zd2 as varchar),'') zd2 , isnull(cast(C.zd3 as varchar),'') zd3 from A left join B on A.id = B.aid left join C on A.id = C.aid where a.id <= 4drop table A,B,C/*
ID zd1 zd2 zd3
----------- ----------- ------------------------------ ------------------------------
1 56
2 43 90
3 65 78 87
4 23 33 (4 行受影响)*/
from A left join B on A.ID=B.AID
left join C on A.ID=C.AID
where ID<=4
INSERT INTO @A SELECT 1,56
UNION ALL
SELECT 2,43
UNION ALL
SELECT 3,65
UNION ALL
SELECT 4,23
UNION ALL
SELECT 5,67DECLARE @B TABLE(AID INT,zd2 INT)
INSERT INTO @B SELECT 3,78
UNION ALL
SELECT 4,33DECLARE @C TABLE(AID INT,zd3 INT)
INSERT INTO @C SELECT 2,90
UNION ALL
SELECT 3,87
UNION ALL
SELECT 5,67select ID,zd1,
zd2 = isnull((select zd2 from @b where a.id = aid),''),
zd3 = isnull((select zd3 from @c where a.id = aid),'')
from @a a
where id <=4
insert into A values(1, 56)
insert into A values(2, 43)
insert into A values(3, 65)
insert into A values(4, 23)
insert into A values(5, 67)
create table B(AID int, zd2 int)
insert into B values(3, 78)
insert into B values(4, 33)
create table C(AID int, zd3 int)
insert into C values(2, 90 )
insert into C values(3, 87 )
insert into C values(5, 67 )
goselect ID, zd1=isnull(rtrim(zd1), ''), zd2=isnull(rtrim(zd2), ''), zd3=isnull(rtrim(zd3), '')
from A left join B on A.ID=B.AID
left join C on A.ID=C.AID
where ID<=4drop table a,b,c/*
ID zd1 zd2 zd3
----------- ------------ ------------ ------------
1 56
2 43 90
3 65 78 87
4 23 33 (4 row(s) affected)
*/
哪个快点呢。我的数据表很多有A、B、C、D、E、F、G
有的表有几百条记录,有的几千条,字段也多。用外部关联LEFT JOIN会超时呀。怎么办????