帮忙把这个sql优化一下，在线等~

[xuxubaby]

SELECT comid,CompanyName,addtime,[status] FROM (select *, ROW_NUMBER() OVER (order by [status] asc)
AS RowNumber FROM job_company
where comid in(select comid from job_job where [status]=1) and addtime='2011-08-31' and addtime>='2011-08-18') t

[geniuswjt]

and addtime='2011-08-31' and addtime>='2011-08-18'

这里啥意思？

[geniuswjt]

--没看出来哪里用到row_number()，固去掉；in改为exists；addtime后面多余的去掉。如下：
SELECT comid,CompanyName,addtime,[status] FROM job_company a
where exists(select 1 from job_job where [status]=1 where comid=a.comid)
and addtime='2011-08-31'

[xuxubaby]

哦 这里应该换成 or ，主要问题是出在in 上 ，用了in ，查询速度就慢了很多.

[AcHerat]

SELECT comid,CompanyName,addtime,[status],ROW_NUMBER() OVER (order by [status] asc) AS RowNumber 
FROM job_company  
where comid in (select comid from job_job where [status]=1) 
	and addtime='2011-08-31' 
	and addtime>='2011-08-18' --这两个日期应该留一个最大的吧！

[--小F--]

SELECT
 comid,CompanyName,addtime,[status] 
FROM
 (select *, ROW_NUMBER() OVER (order by [status] asc) AS RowNumber FROM job_company)t  
where
 exists(select 1 from job_job where comid=t.comid and [status]=1) 
and
 addtime='2011-08-31' 
and
 addtime>='2011-08-18') t

[AcHerat]

SELECT comid,CompanyName,addtime,[status],ROW_NUMBER() OVER (order by [status] asc) AS RowNumber 
FROM job_company t
where exists (select 1 from job_job where [status]=1 and comid = t.comid) 
	and addtime='2011-08-31' 
	or addtime>='2011-08-18'
--其实也不应该用or，你只要大于俩日期最小的就可以，即  addtime>='2011-08-18'

[nvhaixx]

SELECT comid, CompanyName, addtime, status
FROM job_company
where comid in (select t.comid from job_job t where t.status = 1)
and (addtime = '2011-08-31' or addtime >= '2011-08-18')

[中国风]

select 
	comid,CompanyName,addtime,ROW_NUMBER() OVER (order by [status] asc)AS RowNumber --這列有沒有用到，只用排序用order by 
FROM 
	job_company AS a
where exists(select comid from job_job where [status]=1 AND comid=a.comid) 
and (addtime>='2011-08-18' OR addtime='2011-08-31' )

[中国风]

addtime列上建上索引

[xuxubaby]

我建了一个组合索引，status，addtime这2个字段。

[xuxubaby]

哦了 ，3q各位。结贴 ~