日期:2014-05-17 浏览次数:20571 次
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
------解决方案--------------------
更正
;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