34,576
社区成员
发帖
与我相关
我的任务
分享
SELECT ji.[iID] AS ID
FROM et_d_JobInfo ji,et_s_Comp c
WHERE ji.iCompID = c.[iID]
AND ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%') )
AND ((ji.iWorkPlaceID1 LIKE '33%'
OR ji.iWorkPlaceID2 LIKE '33%'
OR ji.iWorkPlaceID3 LIKE '33%') )
AND (c.iIndustryID LIKE '%%' )
AND ji.iSalaryID = 13
AND ji.tiJobTerm = 1
AND ji.tijobstate = 1
AND ji.[iID]
NOT IN (
SELECT [iJobID]
FROM [ps_d_ResumeRec]
WHERE [iUserID] = 395007
AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7)
ORDER BY ID DESC
SELECT ji.[iID] AS ID FROM et_d_JobInfo AS ji
LEFT JOIN et_s_Comp AS c ON ji.iCompID = c.[iID]
WHERE ji.iCompID = c.[iID]
AND ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%') )
AND ((ji.iWorkPlaceID1 LIKE '33%'
OR ji.iWorkPlaceID2 LIKE '33%'
OR ji.iWorkPlaceID3 LIKE '33%') )
AND (c.iIndustryID LIKE '%%' )
AND ji.iSalaryID = 13
AND ji.tiJobTerm = 1
AND ji.tijobstate = 1
AND
NOT Exists (
SELECT [iJobID]
FROM [ps_d_ResumeRec]
WHERE [iUserID] = 395007
--AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7
AND [dtAddTime]<getdate()-7
And ji.[iID]=[iJobID]
)
ORDER BY ID DESC
--这样试下
STEP 1:
CREATE INDEX IDX0_ET_D_JOBINFO ON ET_D_JOBINFO(iCompID);
CREATE INDEX IDX0_ET_S_COMP ON ET_S_COMP(iID);
STEP 2:
SELECT
A.iID
FROM et_d_JobInfo A
INNER JOIN
(
SELECT
iID
FROM et_s_Comp B1
WHERE iIndustryID LIKE '%%'--没看懂你这是LIKE 什么呢?是所有值吗,小弟才学疏浅,没这么写过
AND NOT EXISTS(
SELECT
1
FROM ps_d_ResumeRec B2 --此表数据量大吗,大的话可以考虑iJobID建索引
WHERE iUserID=395007
AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7)
AND B1.iID=B2.iJobID
)
)B
ON A. iCompID=B.iID
WHERE
AND A.iSalaryID = 13
AND A.tiJobTerm = 1
AND A.tijobstate = 1
AND (A.iFunTypeID1 LIKE '%%' OR A.iFunTypeID2 LIKE '%%') --同上
AND (A.iWorkPlaceID1 LIKE '33%' OR A.iWorkPlaceID2 LIKE '33%' OR A.iWorkPlaceID3 LIKE '33%')
ORDER BY A.iID DESC
--修改
--iWorkPlaceID1,iWorkPlaceID2,iWorkPlaceID3建立索引
SELECT [iJobID] into #tb
FROM [ps_d_ResumeRec]
WHERE [iUserID] = 395007
AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7
SELECT ji.[iID] AS ID
FROM et_d_JobInfo ji
inner join et_s_Comp c on ji.iCompID = c.[iID]
left join #tb d on ji.[iID]=d.[iJobID]
WHERE ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%'))---这个啥意思。。
AND ((ji.iWorkPlaceID1 LIKE '33%'
OR ji.iWorkPlaceID2 LIKE '33%'
OR ji.iWorkPlaceID3 LIKE '33%') )
AND (c.iIndustryID LIKE '%%' )
AND ji.iSalaryID = 13
AND ji.tiJobTerm = 1
AND ji.tijobstate = 1 and d.[iJobID] is null
ORDER BY ID DESC
drop table #tb
--iWorkPlaceID1,iWorkPlaceID2,iWorkPlaceID3建立索引
SELECT [iJobID] into #tb
FROM [ps_d_ResumeRec]
WHERE [iUserID] = 395007
AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7
SELECT ji.[iID] AS ID
FROM et_d_JobInfo ji,et_s_Comp c,#tb d
WHERE ji.iCompID = c.[iID] and ji.[iID]=d.[iJobID]
AND ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%') )
AND ((ji.iWorkPlaceID1 LIKE '33%'
OR ji.iWorkPlaceID2 LIKE '33%'
OR ji.iWorkPlaceID3 LIKE '33%') )
AND (c.iIndustryID LIKE '%%' )
AND ji.iSalaryID = 13
AND ji.tiJobTerm = 1
AND ji.tijobstate = 1 and d.[iJobID] is null
ORDER BY ID DESC
drop table #tb