select '初中部' Dept,'老师' SName,null Price,sum(Price) CLConsume,sum(Price) SumPrice
from dbo.T_H 
where EMPID='00000' 
union all

select '初中部' Dept,'一年级' SName,sum(Price) Price,null CLConsume,sum(Price) SumPrice
from dbo.T_H
where EMID in (select EM_ID from Base.dbo.Em where SName='一年级' ) 
union all

select '初中部' Dept,'二年级' SName,sum(Price) Price,null CLConsume,sum(Price) SumPrice
from dbo.T_H
where EMID in (select EM_ID from Base.dbo.Em where SName='二年级' ) 
union all
.....一直到 高中部

union all了很多 执行的时候 1分钟以内不会出结果 怎么样优化啊

--参考
SELECT  T_H.Dept ,
        CASE WHEN empid = '00000' THEN '老师'
             ELSE T_H.SName
        END AS Sname ,
        CASE WHEN empid = '00000' THEN NULL
             ELSE SUM(Price)
        END AS Price ,
        CASE WHEN empid = '00000' THEN SUM(Price)
             ELSE NULL
        END AS CLConsume ,
        SUM(Price) SumPrice
FROM    dbo.T_H
        LEFT JOIN Base.dbo.Em BE ON T_H.EMID = BE.EM_ID   --链接的字段上加索引
GROUP BY T_H.Dept ,
        CASE WHEN empid = '00000' THEN '老师'
             ELSE T_H.SName
        END

------解决方案--------------------
更正
SQL code
;with cet1 as(select case when (a.EMPID = '00000' or b.SName in('一年级','二年级','三年级')) then '初中部'
                                                when (a.EMPID = '00001' or b.SName in('高一年级','高二年级','高三年级')) then '高中部' end Dept,
                         case when a.EMPID in('00000','00001') then '老师' else b.SName end SName,Price
        from dbo.T_H a, Base.dbo.Em b where a.EMID = b.EM_ID 
        where b.b.SName in('一年级','二年级','三年级','高一年级','高二年级','高三年级')
)
select Dept, SName,sum( case when Dept = '老师' then 0 else Price end) Price,
                                                                         sum( case when Dept = '老师' then Price else 0 end) CLConsume,
                                                                            sum(Price) SumPrice from CET1
group by Dept, SName