数据库为:sql server
我现在有这样的一条SQL语句:表中现有数据8万左右
select distinct des,
(select count(*) from baseinfo as a where a.des=b.des and(jjgz='1' or jjgz='2') ) as count1,
(select count(*) from baseinfo as a where a.des=b.des and jjgz is not null ) as count2,
case when (select count(*) from baseinfo as a where a.des=b.des and jjgz is not null )=0
then 0
else (cast(cast((select count(*) from baseinfo as a where a.des=b.des and(jjgz='1' or jjgz='2') ) as float)/(
select count(*) from baseinfo as a where a.des=b.des and jjgz is not null ) as decimal(18,6)) )*100 end as cout1
from baseinfo as b where 1=1 order by cout1 desc
在查询的时候用时6秒。
同时也为该字段建了索引,也没有提高查询速度。
请问各路高手,我该如何优化下呢?让查询速度变的快点。
我现在有这样的一条SQL语句:表中现有数据8万左右
select distinct des,
(select count(*) from baseinfo as a where a.des=b.des and(jjgz='1' or jjgz='2') ) as count1,
(select count(*) from baseinfo as a where a.des=b.des and jjgz is not null ) as count2,
case when (select count(*) from baseinfo as a where a.des=b.des and jjgz is not null )=0
then 0
else (cast(cast((select count(*) from baseinfo as a where a.des=b.des and(jjgz='1' or jjgz='2') ) as float)/(
select count(*) from baseinfo as a where a.des=b.des and jjgz is not null ) as decimal(18,6)) )*100 end as cout1
from baseinfo as b where 1=1 order by cout1 desc
在查询的时候用时6秒。
同时也为该字段建了索引,也没有提高查询速度。
请问各路高手,我该如何优化下呢?让查询速度变的快点。
distinct des,
sum(case when jjgz='1' or jjgz='2' then 1 else 0 end) as count1,
sum(case when jjgz is not null then 1 else 0 end) as count2,
...
from baseinfo as b where 1=1 order by cout1 desc
FROM
(
select des,
sum(case when jjgz=1 or jjgz=2 then 1 else 0 end) count1,
sum(case when jjgz is null then 1 else 0 end) count2,
sum(case when jjgz is not null then 1 else 0 END) count3
from baseinfo
group by des
) aa
order by count1 DESC
count(case when b.jjgz='1' or b.jjgz='2' then b.des end) as count1,
count(case when b.jjgz is not null then b.des end) as count2,
case when count(case when b.jjgz is not null then b.des end)=0 then 0
else cast(cast(count(case when b.jjgz='1' or b.jjgz='2' then b.des end) as float)
/count(case when b.jjgz is not null then b.des end) as decimal(18,6))*100
end as cout1
from baseinfo as b
left join baseinfo as a
on a.des=b.des
where 1=1
order by cout1 desc这样试试