导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

简单的SQL语句问题?

sujiang 2007-12-20 11:26:33
有表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)只用内关联。
...全文
81 点赞 收藏 10
写回复
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
sujiang 2007-12-20
用外部关联left join和不用外部关联。
哪个快点呢。我的数据表很多有A、B、C、D、E、F、G
有的表有几百条记录,有的几千条,字段也多。用外部关联LEFT JOIN会超时呀。怎么办????
回复
dobear_0922 2007-12-20
create table A(ID int,             zd1 int)
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 )
go

select 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<=4

drop table a,b,c

/*
ID zd1 zd2 zd3
----------- ------------ ------------ ------------
1 56
2 43 90
3 65 78 87
4 23 33

(4 row(s) affected)
*/
回复
areswang 2007-12-20
DECLARE @A TABLE(ID INT,ZD1 INT)
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,67

DECLARE @B TABLE(AID INT,zd2 INT)
INSERT INTO @B SELECT 3,78
UNION ALL
SELECT 4,33

DECLARE @C TABLE(AID INT,zd3 INT)
INSERT INTO @C SELECT 2,90
UNION ALL
SELECT 3,87
UNION ALL
SELECT 5,67

select 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
回复
dobear_0922 2007-12-20
select ID, zd1, zd2, zd3  
from A left join B on A.ID=B.AID
left join C on A.ID=C.AID
where ID<=4
回复
dawugui 2007-12-20
create table A(ID int,             zd1 int)
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 )
go

select 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 <= 4

drop table A,B,C

/*
ID zd1 zd2 zd3
----------- ----------- ------------------------------ ------------------------------
1 56
2 43 90
3 65 78 87
4 23 33

(4 行受影响)

*/
回复
-狙击手- 2007-12-20
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
where id <=4
回复
-狙击手- 2007-12-20
 
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
回复
dawugui 2007-12-20
create table A(ID int,             zd1 int)
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 )
go

select 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 <= 4

drop 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 行受影响)
*/
回复
$扫地僧$ 2007-12-20

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

回复
tim_spac 2007-12-20
楼主不必担心,SQL Server会帮你做一些优化。只要相关的ID做好索引(最好是PK/Unique),应该不会超时
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告