记一次sqlserver性能调优，查询从20秒至2秒

select *from(
    select *,ROW_NUMBER() OVER (ORDER BY sc desc) as rank  
    from(
        select *,
                case when ( 40-(a.p*(case when a.p > 0 then 1 else -0.5 end)))<=30 
                        then 30 
                     else ( 40-(a.p*(case when a.p > 0 then 1 else -0.5 end))) 
                     end as sc   
        from (
        select * from (
                select a.ProjectNumber, a.ProjectName, a.BaojianNumber, a.BaojianName, a.ToubiaoPerson,
                    sum(UnitPrice) as sumPrice, 
                    b.price as avgPrice, 
                    ((sum(UnitPrice)-b.price)/nullif(b.price,0)*100) as p,
                    sum(case when UnitPrice>b.price then b.price else UnitPrice end )as pprice,
                    sum(case when UnitPrice>MaxPrice then 1 else 0 end ) as countChao
                from ToubiaoDetailTest1 a
                        join (
                            select ProjectNumber, ProjectName, BaojianNumber, BaojianName, avg(price) as price
                            from(
                                select * from(
                                    select ProjectNumber, ProjectName, BaojianNumber, BaojianName, ToubiaoPerson, 
                                        SUM(UnitPrice) as price,
                                        SUM(case when UnitPrice>MaxPrice then 1 else 0 end ) as countChao
                                    from ToubiaoDetailTest1  
                                    group BY ProjectNumber, ProjectName, BaojianNumber, BaojianName, ToubiaoPerson
                                ) tt 
                                where tt.countChao = 0
                            ) t
                            group by ProjectNumber, ProjectName, BaojianNumber, BaojianName
                        ) b 
                            on a.ProjectNumber=b.ProjectNumber and a.ProjectName=b.ProjectName and a.BaojianNumber=b.BaojianNumber and a.BaojianName=b.BaojianName
                group by a.BaojianNumber, a.BaojianName, a.ProjectNumber, a.ProjectName, a.ToubiaoPerson, b.price    
            ) tt 
            where tt.countChao=0
        ) a  
    ) b
) t   
order by rank