34,590
社区成员
发帖
与我相关
我的任务
分享
select top 15 *
from Client --客户表
LEFT JOIN InfoSourceCategory ON InfoSourceCategory.PKID = Client.FK_InfoSourceCategory AND InfoSourceCategory.IsValid = 1
LEFT JOIN CallHistory ON CallHistory.PKID = Client.FK_CallHistory AND CallHistory.IsValid = 1 --访问记录表
LEFT JOIN Dictionary A ON A.PKID = CallHistory.FK_Center AND A.IsValid = 1
LEFT JOIN Dictionary B ON B.PKID = Client.FK_ExtendCenter AND B.IsValid = 1
where 1=1 AND ((Client.FK_Center IN(61) OR A.PKID IN (61)) AND Client.IsValid = 1 ) --***影响性能的地方
order by Client.PKID desc
select top 15 *
from Client --客户表
LEFT JOIN InfoSourceCategory ON InfoSourceCategory.PKID = Client.FK_InfoSourceCategory AND InfoSourceCategory.IsValid = 1
LEFT JOIN InfoSourceDetail ON InfoSourceDetail.PKID = Client.FK_InfoSourceDetail AND InfoSourceDetail.IsValid = 1
LEFT JOIN DataImportType ON DataImportType.PKID = Client.FK_DataImportType
LEFT JOIN VisitType ON VisitType.PKID = Client.FK_VisitType
LEFT JOIN CallHistory ON CallHistory.PKID = Client.FK_CallHistory AND CallHistory.IsValid = 1 --访问记录表
LEFT JOIN Dictionary A ON A.PKID = CallHistory.FK_Center AND A.IsValid = 1
LEFT JOIN Dictionary B ON B.PKID = Client.FK_ExtendCenter AND B.IsValid = 1
LEFT JOIN InboundResult ON InboundResult.PKID = CallHistory.FK_InboundResult AND InboundResult.IsValid = 1
LEFT JOIN OutboundResultType ON OutboundResultType.PKID = CallHistory.FK_OutboundResultType AND OutboundResultType.IsValid = 1
LEFT JOIN OutboundResultDetail ON OutboundResultDetail.PKID = CallHistory.FK_OutboundResultDetail AND OutboundResultDetail.IsValid = 1
LEFT JOIN VisitHistory ON VisitHistory.PKID = Client.FK_VisitHistory AND VisitHistory.IsValid = 1
LEFT JOIN Dictionary C ON C.PKID = VisitHistory.FK_Center AND C.IsValid = 1
LEFT JOIN [User] CC ON CC.PKID = Client.FK_CC AND CC.IsValid = 1
LEFT JOIN [User] Tutor ON Tutor.PKID = Client.FK_Tutor AND Tutor.IsValid = 1
INNER JOIN Contract ON Contract.FK_Client = Client.PKID AND Contract.IsValid = 1
INNER JOIN ContractStatusType ON ContractStatusType.PKID = Contract.FK_ContractStatusType AND ContractStatusType.IsValid = 1 -- and ContractStatusType.pkid = 2
LEFT JOIN CourseLevel BeginLevel ON BeginLevel.PKID = Contract.FK_BeginLevel AND BeginLevel.IsValid = 1
LEFT JOIN CourseLevel CurrentLevel ON CurrentLevel.PKID = Contract.FK_CurrentLevel AND CurrentLevel.IsValid = 1
LEFT JOIN CourseLevel EndLevel ON EndLevel.PKID = Contract.FK_EndLevel AND EndLevel.IsValid = 1
LEFT JOIN ContractType ON ContractType.PKID = Contract.FK_ContractType AND ContractType.IsValid = 1
where 1=1 AND ((Client.FK_Center IN(61) OR A.PKID IN (61)) AND Client.IsValid = 1 )
and (1=1 AND (Client.PKID IN (SELECT FK_Client FROM Contract WHERE FK_ContractStatusType = 2 AND IsValid = 1) ))
--因为合同表中一个客户存在2个合同的情况,而且状态不一致。所以此处在=2的条件和在ContractStatusType连接时加=2的条件所产生的结果是不一样的
order by Client.PKID desc