sql查询中添加了top后执行的速度慢
--发病时间病例数统计
select aTime, max(case Cases_Sex when 1 then aCount else 0 end) as 男
,max(case Cases_Sex when 0 then aCount else 0 end) as 女
,
max(case job when '23009001' then bCount else 0 end),max(case job when '23009002' then bCount else 0 end),max(case job when '23009003' then bCount else 0 end),max(case job when '23009004' then bCount else 0 end),max(case job when '23009007' then bCount else 0 end),max(case job when '23009006' then bCount else 0 end),max(case job when '23009008' then bCount else 0 end),max(case job when '23009009' then bCount else 0 end),max(case job when '23009010' then bCount else 0 end),max(case job when '23009011' then bCount else 0 end),max(case job when '23009012' then bCount else 0 end),c.allcount
from (select aTime as ctime ,sum(aCount) as allcount from
(
select CONVERT(varchar(7),Cases_SickTime,120) as aTime , Cases_Sex ,count(*) as aCount
from VW_Sta_Case_Info_CaseStatistics where 1=1 and Unit_Province=15 group by CONVERT(varchar(7),Cases_SickTime,120), Cases_Sex
) as a
group by aTime )as c inner join
(
select CONVERT(varchar(7),Cases_SickTime,120) as aTime , Cases_Sex ,count(*) as aCount
from VW_Sta_Case_Info_CaseStatistics where 1=1 and Unit_Province=15 group by CONVERT(varchar(7),Cases_SickTime,120), Cases_Sex
) as d
on aTime=cTime left join
(
select CONVERT(varchar(7),Cases_SickTime,120) as bTime , Cases_Occupation as job ,count(*) as bCount
from VW_Sta_Case_Info_CaseStatistics where 1=1 and Unit_Province=15 group by CONVERT(varchar(7),Cases_SickTime,120), Cases_Occupation
) as b
on aTime=bTime
where 1=1
group by aTime,allcount
数据大概十几万条，执行时间1秒，
添加上top 10 后执行时间40秒
怎么修改
[解决办法]
这个和top无关吧,看你关联这么复杂,想快都难呀
[解决办法]
建议少一些子查询，多增加几张临时表，这样执行效率会加快，尤其是这些：
from (select aTime as ctime ,sum(aCount) as allcount from
(
select CONVERT(varchar(7),Cases_SickTime,120) as aTime , Cases_Sex ,count(*) as aCount
from VW_Sta_Case_Info_CaseStatistics where 1=1 and Unit_Province=15 group by CONVERT(varchar(7),Cases_SickTime,120), Cases_Sex
) as a
group by aTime )as c inner join
(
select CONVERT(varchar(7),Cases_SickTime,120) as aTime , Cases_Sex ,count(*) as aCount
from VW_Sta_Case_Info_CaseStatistics where 1=1 and Unit_Province=15 group by CONVERT(varchar(7),Cases_SickTime,120), Cases_Sex
) as d
on aTime=cTime left join
(
select CONVERT(varchar(7),Cases_SickTime,120) as bTime , Cases_Occupation as job ,count(*) as bCount
from VW_Sta_Case_Info_CaseStatistics where 1=1 and Unit_Province=15 group by CONVERT(varchar(7),Cases_SickTime,120), Cases_Occupation
) as b
on aTime=bTime
where 1=1
group by aTime,allcount
都放入临时表，然后再查询临时表吧
[解决办法]
建几个索引，按条件建，写搜索语句，然后sql关键字有先后顺序，参考
http://blog.jobbole.com/55086/。如果数据库能动，上面也可以试试
