我有四个表,A,B,C,D(MS SQL数据库的),分别是A表:
ID COMPANY CLASS_B_ID CLASS_C_ID CLASS_D_ID 分别是 ID(加一自动增长),公司名(nvarchar),后面三个字段是另三个表的ID值
B表:
ID CLASSNAME_B
C表:
ID CLASSNAME_C
D表:
ID CLASSNAME_D我现在想做的是,做一个视图,把四个表都关联起来,并成一个表,读取这个合并后的表后可以直接输入ID COMPANY CLASSNAME_B CLASSNAME_C CLASSNAME_D请问怎么写这个视图?
ID COMPANY CLASS_B_ID CLASS_C_ID CLASS_D_ID 分别是 ID(加一自动增长),公司名(nvarchar),后面三个字段是另三个表的ID值
B表:
ID CLASSNAME_B
C表:
ID CLASSNAME_C
D表:
ID CLASSNAME_D我现在想做的是,做一个视图,把四个表都关联起来,并成一个表,读取这个合并后的表后可以直接输入ID COMPANY CLASSNAME_B CLASSNAME_C CLASSNAME_D请问怎么写这个视图?
as
select ID , COMPANY, CLASSNAME_B, CLASSNAME_C, CLASSNAME_D
from a join b on a.CLASS_B_ID =b.id and
a join c on a.CLASS_B_ID =c.id and
a join d on a.CLASS_B_ID =d.id
as
select ID , COMPANY, CLASSNAME_B, CLASSNAME_C, CLASSNAME_D
from a join b on a.CLASS_B_ID =b.id and
join c on a.CLASS_B_ID =c.id and
join d on a.CLASS_B_ID =d.id
ASSELECT
A.ID,CLASSNAME_B ,CLASSNAME_C ,CLASSNAME_D
FROM A ,B,C
WHERE A.CLASS_B_ID =B.CLASS_B_ID AND
A.CLASS_B_ID =C.CLASS_B_ID AND
A.CLASS_B_ID =D.CLASS_B_ID
as
select a.ID , a.COMPANY, b.CLASSNAME_B, c.CLASSNAME_C, d.CLASSNAME_D
from a inner join b on a.CLASS_B_ID =b.id and
a inner join c on a.CLASS_B_ID =c.id and
a inner join d on a.CLASS_B_ID =d.id
ASSELECT
A.ID,A.COMPANY,CLASSNAME_B ,CLASSNAME_C ,CLASSNAME_D
FROM A ,B,C
WHERE A.CLASS_B_ID =B.CLASS_B_ID AND
A.CLASS_B_ID =C.CLASS_B_ID AND
A.CLASS_B_ID =D.CLASS_B_ID
还少了个COMPANY
如果A表还有很多字段,我想一次性都读出来,是不是
create view kk
as
select * from a join b on a.CLASS_B_ID =b.id and
join c on a.CLASS_B_ID =c.id and
join d on a.CLASS_B_ID =d.id还有,如果B、C、D的CLASS_B等字段名都是用CLASSNAME来命名的话,应该怎么写
要指定B.C,D中的字段呀,A中字段可以不用指定,如果有重名的,用A.指定
as
select customer.ID,adminuser.aeaoa_name,wenjianming.wenjianming,class_source.s_name,class_xxlx.ClassName,class_type.type
from customer join adminuser on customer.aeaoa_name =adminuser.id and
join wenjianming on customer.wjm_id =wenjianming.id and
join class_source on customer.Sourceid =class_source.id and
join class_xxlx on customer.xxlx =class_xxlx.id我这样写的,但提示错误:'JOIN'附近的 ON 子句错误。
无法对查询文本做语法分析。
如果a.CLASS_B_ID中的为空或者在c.id中找不到,会不会出错的
as
select customer.ID,adminuser.aeaoa_name,wenjianming.wenjianming,class_source.s_name,class_xxlx.ClassName,class_type.type
from customer join adminuser on customer.aeaoa_name =adminuser.id and
join wenjianming on customer.wjm_id =wenjianming.id and
join class_source on customer.Sourceid =class_source.id and
join class_xxlx on customer.xxlx =class_xxlx.id 这里不用AND
as
select ID , COMPANY, CLASSNAME_B, CLASSNAME_C, CLASSNAME_D
from a join b on a.CLASS_B_ID =b.id
join c on a.CLASS_B_ID =c.id
join d on a.CLASS_B_ID =d.id
不会阿。你觉得会出什么错误
那可以这样create view kk
as
select ID=a.CLASS_B_ID , COMPANY, CLASSNAME_B, CLASSNAME_C, CLASSNAME_D
from a left join b on a.CLASS_B_ID =b.id
left join c on a.CLASS_B_ID =c.id
left join d on a.CLASS_B_ID =d.id
出现这个提示:查询设计器不支持 CREATE VIEW SQL 构造。