试试
create view v_custListShowInfo
as
select A.numCustId,
numCustKind=case A.numCustKind when 0 then N'目标客户' else N'现有客户' end,
B.nvcCustTypeName,
A.nvcCustName,
nvcCustTel=isnull(A.nvcTelDistCode,'')+case A.nvcTelDistCode when '' then '' else '-' end+A.nvcTelNo,
ISNULL(C.nvcContPerson,'-'),
ISNULL(C.nvcContDept,'-'),
nvcCustContactTel=ISNULL(isnull(C.nvcContTelCode,'')+case C.nvcContTelCode when '' then '' else '-' end+C.nvcContTelNo,'-')
From
tbCustomerBasic as A JOIN tbVarCustomerType as B
ON A.numCustTypeId=B.numCustTypeId
LEFT JOIN tbCustomerContact as C
ON A.numCustId=C.numCustId
where
C.numContId=(select top 1 numContId
from tbCustomerContact
where numCustId=A.numCustId)
create view v_custListShowInfo
as
select A.numCustId,
numCustKind=case A.numCustKind when 0 then N'目标客户' else N'现有客户' end,
B.nvcCustTypeName,
A.nvcCustName,
nvcCustTel=isnull(A.nvcTelDistCode,'')+case A.nvcTelDistCode when '' then '' else '-' end+A.nvcTelNo,
ISNULL(C.nvcContPerson,'-'),
ISNULL(C.nvcContDept,'-'),
nvcCustContactTel=ISNULL(isnull(C.nvcContTelCode,'')+case C.nvcContTelCode when '' then '' else '-' end+C.nvcContTelNo,'-')
From
tbCustomerBasic as A JOIN tbVarCustomerType as B
ON A.numCustTypeId=B.numCustTypeId
LEFT JOIN tbCustomerContact as C
ON A.numCustId=C.numCustId
where
C.numContId=(select top 1 numContId
from tbCustomerContact
where numCustId=A.numCustId)
而我还是要显示A表的信息比如 客户ID 455 客户名称: 天天向上公司
C 表 客户联系人我都删除了但我显示的时候 要 显示 客户ID 455 客户名称: 天天向上公司 联系人部分都用 ‘-’ 表示没有联系人 怎么关联 才能啊msn [email protected] 急啊 被经理找的毛病 帮忙啊!!!!!!
as
select A.numCustId,
numCustKind=case A.numCustKind when 0 then N'目标客户' else N'现有客户' end,
B.nvcCustTypeName,
A.nvcCustName,
nvcCustTel=isnull(A.nvcTelDistCode,'')+case A.nvcTelDistCode when '' then '' else '-' end+A.nvcTelNo,
C.nvcContPerson,
C.nvcContDept,
nvcCustContactTel=isnull(C.nvcContTelCode,'')+case C.nvcContTelCode when '' then '' else '-' end+C.nvcContTelNo
From
tbCustomerBasic as A
LEFT JOIN tbCustomerContact C
ON
a.numCustId=C.numCustId
C.numContId=(select top 1 numContId
from tbCustomerContact
where numCustId=A.numCustId) tbVarCustomerType as Bwhere
A.numCustTypeId=B.numCustTypeId 差不多吧 你看看
这个时候我还要继续读出A表中的相关记录 但是C里面的字段就用 ‘-’表示了
From
tbCustomerBasic as A JOIN tbVarCustomerType as B
ON A.numCustTypeId=B.numCustTypeId
LEFT JOIN tbCustomerContact as C
ON A.numCustId=C.numCustId
where
C.numContId=(select top 1 numContId from tbCustomerContact where numCustId=A.numCustId)
OR (isnull((select top 1 numContId from tbCustomerContact where numCustId=A.numCustId),'1')='1'
注意:多加一个条件,当C表为空时,照样能得到A的数据。至于显示为‘-’,放页面中绑定的时候用IIF(nvcContPerson<>'',nvcContPerson,'-')就可以了