sql语句优化问题

弦弦 2011-09-30 03:37:20



SELECT soft_id,(SELECT COUNT(distinct(uc_softrecord.user_id))AS A FROM uc_softrecord where 

uc_softrecord.soft_id='2' and uc_softrecord.id='511331') AS ClickPeoNumber,

(select uc_softrecord.click from uc_softrecord where uc_softrecord.soft_id='2' and uc_softrecord.id='511331') AS

 ClickNumber,

(SELECT count(distinct(user_id) ) as DropNumber  FROM uc_softrecord where soft_id='2' and uc_softrecord.`status`

 in (1,2) and uc_softrecord.id='511331') AS DropNumber,

(Select count(distinct(user_id) ) AS num from uc_softrecord where uc_softrecord.`status`='2' and 

uc_softrecord.soft_id='2' and uc_softrecord.id='511331') AS DropOkNumber

 FROM uc_softrecord  where uc_softrecord.soft_id='2' and uc_softrecord.id='511331' and uc_softrecord.createtime

 between '2011-09-15' and '2011-09-30'

别说建索引，视图，什么的。。。就提论题，优化这个sql语句，

...全文

119 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

弦弦 2011-09-30

打赏
举报

不太可能。因为uc_softrecord.soft_id 和uc_softrecord.id值是变化的。

xiangaylian 2011-09-30

打赏
举报

每一列都是一个统计的数据，每一个统计的条件都不一样，楼主可能是想在方式方法上面去优化。
那join的话估计就很难做到了。
提议一种方式：
建立一个临时表，将uc_softrecord.soft_id='2' and uc_softrecord.id='511331'的所有查询结果先放到临时表中，然后上面的查询都从临时表中查询，那么可以去掉所有的uc_softrecord.soft_id='2' and uc_softrecord.id='511331'条件了。

xiangaylian 2011-09-30

打赏
举报



SELECT soft_id,

(

	SELECT COUNT(distinct(uc_softrecord.user_id))AS A FROM uc_softrecord 

		where uc_softrecord.soft_id='2' and uc_softrecord.id='511331'

) AS ClickPeoNumber,

(

	select uc_softrecord.click from uc_softrecord where uc_softrecord.soft_id='2' and uc_softrecord.id='511331'

) AS ClickNumber,

(

	SELECT count(distinct(user_id)) as DropNumber FROM uc_softrecord 

		where soft_id='2' and uc_softrecord.`status` in (1,2) and uc_softrecord.id='511331'

) AS DropNumber,

(

	Select count(distinct(user_id)) AS num from uc_softrecord 

		where uc_softrecord.`status`='2' and uc_softrecord.soft_id='2' and uc_softrecord.id='511331'

) AS DropOkNumber

FROM uc_softrecord  

where uc_softrecord.soft_id='2' and uc_softrecord.id='511331' and uc_softrecord.createtime 

	between '2011-09-15' and '2011-09-30'

-------------------------------------------------------------------
写成这样是否会清晰点??

IE11下面经常卡到爆 2011-09-30

打赏
举报

试下用内存表然后JOIN
http://blog.csdn.net/wanjichun/article/details/4466163

laowang134 2011-09-30

打赏
举报

mysql!~不会！·

弦弦 2011-09-30

打赏
举报

忘了说了 mysql数据库。

laowang134 2011-09-30

打赏
举报

uc_softrecord.`status`这个是什么写法！~。。。。mysql？？

IE11下面经常卡到爆 2011-09-30

打赏
举报

from cte right join uc_softrecord on cte.user_Id=uc.user_id where

IE11下面经常卡到爆 2011-09-30

打赏
举报

SELECT COUNT(distinct(uc_softrecord.user_id))AS A FROM uc_softrecord where
uc_softrecord.soft_id='2' and uc_softrecord.id='511331'
这个查了多次完全没必要，改成join吧

2005或2008以上可以用CTE提取出来

;with cte

as

{

    select distinct(uc_softrecord.user_id) AS A FROM uc_softrecord where 

uc_softrecord.soft_id='2' and uc_softrecord.id='511331'

}

select cte.soft_id,

(select Count(cte.A) AS ClickPeoNumber from cte INNER JOIN uc_softrecord as uc

on cte.user_Id=uc.user_id where uc_softrecord.`status`='1' or uc_softrecord.`status`

='2') AS DropNumber,

(select Count(cte.A) from cte where uc_softrecord.`status`='2') AS DropOkNumber ,

from right join uc_softrecord on cte.user_Id=uc.user_id where

uc_softrecord.createtime between '2011-09-15' and '2011-09-30'

laowang134 2011-09-30

打赏
举报

有数据没！~。。。

弦弦 2011-09-30

打赏
举报

SELECT soft_id,(SELECT COUNT(distinct(uc_softrecord.user_id))AS A FROM uc_softrecord where
uc_softrecord.soft_id='2' and uc_softrecord.id='511331') AS ClickPeoNumber,

我如果不加and uc_softrecord.id='511331' 这句话的话 mysql提示行数大于1，

laowang134 2011-09-30