现在有A(人员表),B(来华事由表),C(签证种类表) 3个表
A表中有name ,sex,birthday,qzzl(签证种类),lhsy(来华事由),等字段 其中是qzzl和lhsy中存的是分别的id号
B表中有lhsy_id,lhsy_bm(编码),lhsy_lhsy(来华种类名称)
C表中有qzzl_id,qzzl_bm(编码),qzzl_qzzl(签证种类名称)
我想通过一个查询语句,查出A表中的所有,其中qzzl和lhsy显示出名称 而不是ID号谢谢 帮下
A表中有name ,sex,birthday,qzzl(签证种类),lhsy(来华事由),等字段 其中是qzzl和lhsy中存的是分别的id号
B表中有lhsy_id,lhsy_bm(编码),lhsy_lhsy(来华种类名称)
C表中有qzzl_id,qzzl_bm(编码),qzzl_qzzl(签证种类名称)
我想通过一个查询语句,查出A表中的所有,其中qzzl和lhsy显示出名称 而不是ID号谢谢 帮下
来华种类名称 = (select lhsy_lhsy from B where lhsy_lhsy = lhsy),
签证种类名称 = (select qzzl_qzzl from C where qzzl_qzzl = qzzl)
from A
FROM A
JOIN C
ON A.qzzl = C.qzzl
JOIN B
ON A.lhsy = B.lhsy
from A left join B on A.lhsy=C.lhsy_id
left join C on A.qzzl=C.qzzl_id
select a.*,b.lhsy_lhsy,c.qzzl_qzzl from A a left outer join B b on a.lhsy=b.lhsy_id left outer join C c on a.qzzl=c.qzzl_id
from A left join B on A.lhsy=C.lhsy_id
left join C on A.qzzl=C.qzzl_id
基本就是一个联合查询既可以搞定,联合查询可以让你查出三张表中相关联的所有值,其中各个字段的名字也可以重新命名,例如qzzl_qzzl name as 'qzzl'
SELECT f.wjry_id,f.wjry_ywm,f.wjry_ywx,f.wjry_zwm,a.xb_xb,f.wjry_csrq,b.gjdq_gjdq,c.zjzl_zjzl,d.qzzl_qzzl,f.wjry_qzyxq,f.wjry_ddrq,f.wjry_lkrq,e.tlsy_tlsy
FROM ga_wjry f LEFT JOIN ga_xb a ON f.wjry_xb=a.xb_id
LEFT JOIN ga_gjdq b ON f.wjry_gjdq=b.gjdq_id
LEFT JOIN ga_zjzl c ON f.wjry_qzzl=d.qzzl_id
LEFT JOIN ga_qzzl d ON f.wjry_zjzl=c.zjzl_id
LEFT JOIN ga_tlsy e ON f.wjry_tlsy=e.tlsy_id我这语句有什么语法上的错误吗?
我想通过一个查询语句,查出A表中的所有,其中qzzl和lhsy显示出名称 而不是ID号 .左联可能导致qzzl和lhsy为空时也会显示出来.还是用内联好.支持梁哥的
insert into @em
select 'a','男','1990-01-01','1','1' union all
select 'b','男','1988-01-02','1','2' union all
select 'c','女','1990-03-05','2','2' union all
select 'd','女','1990-08-09','2','2' declare @qzzl table(qzzl_id int,qzzl_bm varchar(50),qzzl_qzzl varchar(50))
insert into @qzzl
select '1','1','出境证' union all
select '2','3','旅游证' union all
select '3','3','xx'declare @lhsy_bm table(lhsy_id int,lhsy_bm varchar(50),lhsy_lhsy varchar(50))
insert into @lhsy_bm
select '1','1','出差' union all
select '2','2','旅游' union all
select '3','3','投资' union all
select '4','4','常驻'select e.names,e.sex,e.birthday,q.qzzl_qzzl,l.lhsy_lhsy from @em e left join @qzzl q on e.qzzl=q.qzzl_id left join @lhsy_bm l on e.lhsy=l.lhsy_id
insert into @em
select 'a','男','1990-01-01','1','1' union all
select 'b','男','1988-01-02','1','2' union all
select 'c','女','1990-03-05','2','2' union all
select 'd','女','1990-08-09','2','2' declare @qzzl table(qzzl_id int,qzzl_bm varchar(50),qzzl_qzzl varchar(50))
insert into @qzzl
select '1','1','出境证' union all
select '2','3','旅游证' union all
select '3','3','xx'declare @lhsy_bm table(lhsy_id int,lhsy_bm varchar(50),lhsy_lhsy varchar(50))
insert into @lhsy_bm
select '1','1','出差' union all
select '2','2','旅游' union all
select '3','3','投资' union all
select '4','4','常驻'select e.names,e.sex,e.birthday,q.qzzl_qzzl,l.lhsy_lhsy from @em e left join @qzzl q on e.qzzl=q.qzzl_id left join @lhsy_bm l on e.lhsy=l.lhsy_id