系统数据的复合sql语句

--需要查询的字段有：客户编号，客户名称，客户手机，施工地址,楼盘名称，面积,
--客户经理，客户经理手机，设计师，设计师手机，主材设计师，主材设计师手机，
--项目经理，项目经理手机，质检，质检人员手机

--客户编号，客户名称，施工地址，楼盘名称，面积可以直接从表sell_khxx中查找出来
Select khbh, khxm,gcdz,lpmc,jzmj,khjl,sjs,zjsjs,xmjl from sell_khxx
--客户经理，设计师，主材设计师，项目经理需要通过表sell_khxx和initialize_per的code字段联合查找
select * from initialize_per
select * from sell_khxx
--查找客户经理的姓名,这里用子查询来查找客户经理的姓名
select khbh as 客户编号,
khxm as 客户姓名,
(select mingcheng from initialize_per where code=a.khjl) as 客户经理
from sell_khxx a where a.khbh=‘2011070000001‘
--查找客户经理,设计师，主材设计师，项目经理的姓名
select khbh as 客户编号,
khxm as 客户姓名,
(select mingcheng from initialize_per where code=a.khjl) as 客户经理,
(select mingcheng from initialize_per where code=a.sjs) as 设计师 ,
(select mingcheng from initialize_per where code=a.zjsjs) as 主材设计师,
(select mingcheng from initialize_per where code=a.xmjl) as 项目经理
from sell_khxx a where a.khbh=‘2011070000001‘

--客户手机，客户经理手机，设计师手机，主材设计师手机，项目经理手机，质检人员手机
select khbh as 客户编号,
khxm as 客户姓名 ,
(select shouji from lianxifangshi where bianma=a.khbh) as 联系电话,
(select shouji from lianxifangshi where bianma=a.khjl) as 客户经理手机,
(select shouji from lianxifangshi where bianma=a.zjsjs) as 主材设计师手机,
(select shouji from lianxifangshi where bianma=a.xmjl) as 项目经理手机
from sell_khxx a where a.khbh=‘2011070000001‘
--查找客户经理,设计师，主材设计师，项目经理的姓名,客户手机，客户经理手机，设计师手机，主材设计师手机，项目经理手机
select khbh as 客户编号,
khxm as 客户姓名 ,
(select shouji from lianxifangshi where bianma=a.khbh) as 联系电话,
gcdz as 施工地址,
lpmc as 楼盘名称,
jzmj as 面积,
(select mingcheng from initialize_per where code=a.khjl) as 客户经理,
(select shouji from lianxifangshi where bianma=a.khjl) as 客户经理手机,
(select mingcheng from initialize_per where code=a.sjs) as 设计师 ,
(select shouji from lianxifangshi where bianma=a.sjs) as 设计师手机 ,
(select mingcheng from initialize_per where code=a.zjsjs) as 主材设计师,
(select shouji from lianxifangshi where bianma=a.zjsjs) as 主材设计师手机,
(select mingcheng from initialize_per where code=a.xmjl) as 项目经理,
(select shouji from lianxifangshi where bianma=a.xmjl) as 项目经理手机
from sell_khxx a where a.khbh=‘2011070000001‘
--质检需要通过表sell_khxx和paigongdan的khbh字段联合查找
--根据派工单中的xiangmujingli字段来查找质检的编号
select xiangmujingli from paigongdan where khbh=‘2011070000001‘
--根据派工单中的xiangmujingli字段来查找质检的名字
select mingcheng from initialize_per where code=(select xiangmujingli from paigongdan where khbh=‘2011070000001‘)
select khbh as 客户编号,
khxm as 客户姓名 ,
(select shouji from lianxifangshi where bianma=a.khbh) as 联系电话,
gcdz as 施工地址,
lpmc as 楼盘名称,
jzmj as 面积,
(select mingcheng from initialize_per where code=a.khjl) as 客户经理,
(select shouji from lianxifangshi where bianma=a.khjl) as 客户经理手机,
(select mingcheng from initialize_per where code=a.sjs) as 设计师 ,
(select shouji from lianxifangshi where bianma=a.sjs) as 设计师手机 ,
(select mingcheng from initialize_per where code=a.zjsjs) as 主材设计师,
(select shouji from lianxifangshi where bianma=a.zjsjs) as 主材设计师手机,
(select mingcheng from initialize_per where code=a.xmjl) as 项目经理,
(select shouji from lianxifangshi where bianma=a.xmjl) as 项目经理手机,
(select mingcheng from initialize_per where code=(select xiangmujingli from paigongdan where khbh=a.khbh)) as 质检,
(select shouji from lianxifangshi where bianma=(select xiangmujingli from paigongdan where khbh=a.khbh))as 质检手机
from sell_khxx a where a.khbh=‘2011070000003‘
--表sell_khxx中能查出的字段
select * from sell_khxx where khbh=‘2011070000003‘
--表lianxifangshi查出客户的电话
select bianma,shouji from lianxifangshi where bianma=‘2011070000003‘
--查询客户经理的手机号
select bianma,shouji from lianxifangshi where bianma=‘PR0011‘