针对你的思路修改,给它们家别名
select a.id,a.name,newtabl1.data,newtable2.data,newtable3.data
from a,
(select t1.id,t1.data from b t1 where t1.pk_b_1='11') newtable1,
(select t2.id,t2.data from b t2 where t2.pk_b_1='22') newtable2,
(select t3.id,t3.data from b t3 where t3.pk_b_1='33') newtable3
where a.id=newtable1.id
and a.id*=newtable2.id
and a.id*=newtable3.id
select a.id,a.name,newtabl1.data,newtable2.data,newtable3.data
from a,
(select t1.id,t1.data from b t1 where t1.pk_b_1='11') newtable1,
(select t2.id,t2.data from b t2 where t2.pk_b_1='22') newtable2,
(select t3.id,t3.data from b t3 where t3.pk_b_1='33') newtable3
where a.id=newtable1.id
and a.id*=newtable2.id
and a.id*=newtable3.id
解决方案 »
- 基础问题:关于SQL查询,应该把哪个字段做建索引。(在线问,看到答案马上给分)
- 关于数据量非常大,拆表时,查询和排序该如何处理?
- 求SQL语句
- 初学者请教SQL SERVER 2000中的几个概念问题
- 各位:内存管理的问题
- SQL 得到 每个组的前两名 怎么写
- и璶开数誹库让远祘ㄏノ蔍数誹库该ń设﹚㎡????
- 写的这个根据子Id取父Id的存储过程怎么运行半天没反应?帮我看看错在哪里了?谢谢
- 排程问题
- 求助!!行与行之间怎样相减??
- 如何在存储过程中传入一序列的值?
- 怎么删除除主键外重复的记录?
主要是最后的内链接,如果在where 中取消*,就可以了,但是达不到预想的结果集.
可以执行,只不过结果是:
id,a_data,b_data,c_data,d_data
1 a b1 c1 NULL
2 b b2 c2 NULL
实际上生成newtable?的sql语句是一个联合表取的结果集,实在抱歉!
1 a1
2 a2
3 a3
4 a4
b表c_id,a_id,data 主键为id,a_id对应a表的id
8 1 b1
8 2 b2
8 3 b3 9 1 b11
9 2 b22 对应c表c_id=10的数据没有c表c_id,c_field_1,c_field_2
8 f1 g1
9 f1 g2
10 f1 g3即c表的一条记录对应b表的多条记录,但是有可能b表中没有c表的数据(如c表中c_id=10的),或者在b表中不全(如c表中c_id=9的)
需要组合的结果集为
a_id,a_name,b_data,c_data,d_data
1 a1 b1 b11
2 a2 b2 b22
3 a3 b3想通过如下的sql语句实现
select a.a_id,a.a_name,newtabl1.data,newtable2.data,newtable3.data
from a,
(select b.a_id,b.data from b,c where b.c_id=c.c_id and c.f_field_1='f1' and c.f_field_2='g1') newtable1,
(select b.a_id,b.data from b,c where b.c_id=c.c_id and c.f_field_1='f1' and c.f_field_2='g2') newtable2,
(select b.a_id,b.data from b,c where b.c_id=c.c_id and c.f_field_1='f1' and c.f_field_2='g3') newtable3,
where a.a_id=newtable1.a_id
and a.a_id*=newtable2.a_id
and a.a_id*=newtable3.a_id
但总是提示:服务器: 消息 303,级别 16,状态 1,行 1
表 'b' 是外联接子句的内部成员。如果该表同时也包含在常规联接子句中,那么这种情况是不允许的。if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[a]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[b]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[c]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[c]
GOCREATE TABLE [dbo].[a] (
[a_id] [int] NOT NULL ,
[a_name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[b] (
[c_id] [int] NOT NULL ,
[a_id] [int] NOT NULL ,
[data] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[c] (
[c_id] [int] NOT NULL ,
[c_field_1] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[c_field_2] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[a] WITH NOCHECK ADD
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
(
[a_id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[b] WITH NOCHECK ADD
CONSTRAINT [PK_b] PRIMARY KEY CLUSTERED
(
[c_id],
[a_id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[c] WITH NOCHECK ADD
CONSTRAINT [PK_c] PRIMARY KEY CLUSTERED
(
[c_field_1],
[c_field_2]
) ON [PRIMARY]
GO
应该是:
select a.a_id,a.a_name,newtable1.data,newtable2.data,newtable3.data
from a,
(select b.a_id,b.data from b,c where b.c_id=c.c_id and c.c_field_1='f1' and c.c_field_2='g1') newtable1,
(select b.a_id,b.data from b,c where b.c_id=c.c_id and c.c_field_1='f1' and c.c_field_2='g2') newtable2,
(select b.a_id,b.data from b,c where b.c_id=c.c_id and c.c_field_1='f1' and c.c_field_2='g3') newtable3
where a.a_id=newtable1.a_id
and a.a_id*=newtable2.a_id
and a.a_id*=newtable3.a_id
错误信息:
服务器: 消息 303,级别 16,状态 1,行 1
表 'b' 是外联接子句的内部成员。如果该表同时也包含在常规联接子句中,那么这种情况是不允许的。
(select a_id,data from b where b.c_id=8) newtable1,
(select a_id,data from b where b.c_id=9) newtable2,
(select a_id,data from b where b.c_id=10) newtable3
这样来实现,但我必须再次读取数据库以得到8,9,10这几个数字,所以想一次实现!
谢谢各位!
from a join
(select b.a_id,b.data from b,c where b.c_id=c.c_id and c.c_field_1='f1' and c.c_field_2='g1') newtable1
on a.a_id=newtable1.a_id
left join (select b.a_id,b.data from b,c where b.c_id=c.c_id and c.c_field_1='f1' and c.c_field_2='g2') newtable2
on a.a_id=newtable2.a_id
left join (select b.a_id,b.data from b,c where b.c_id=c.c_id and c.c_field_1='f1' and c.c_field_2='g3') newtable3
on a.a_id=newtable3.a_id