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语句,
都确定了,那不是只有一条数据
你是不是想按照soft_id进行分组统计?
ClickNumber
除了这一段都是聚集函数,难道不会报错?
uc_softrecord.soft_id='2' and uc_softrecord.id='511331') AS ClickPeoNumber, 我如果不加and uc_softrecord.id='511331' 这句话的话 mysql提示行数大于1,
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'
http://blog.csdn.net/wanjichun/article/details/4466163
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'-------------------------------------------------------------------
写成这样是否会清晰点??
那join的话估计就很难做到了。
提议一种方式:
建立一个临时表,将uc_softrecord.soft_id='2' and uc_softrecord.id='511331'的所有查询结果先放到临时表中,然后上面的查询都从临时表中查询,那么可以去掉所有的uc_softrecord.soft_id='2' and uc_softrecord.id='511331'条件了。