求教SQL执行效率

[baggio328]

在下有个sql语句是取得结果点数的。

计算方式如下：

, ISNULL(MAX(CASE TEES.EvalEmpType WHEN 0 THEN TER.EvalPoint END) * 0.2
+ MAX(CASE TEES.EvalEmpType WHEN 3 THEN TER.EvalPoint END) * 0.4
+ (CASE
WHEN MAX(CASE TEES.EvalEmpType WHEN 4 THEN TER.EvalPoint END) IS NULL
THEN MAX(CASE TEES.EvalEmpType WHEN 3 THEN TER.EvalPoint END) * 0.4
ELSE MAX(CASE TEES.EvalEmpType WHEN 4 THEN TER.EvalPoint END) * 0.4
END)
, 0) AS POINT2

但是这个速度特别慢，只抽取这个结果就需要10秒左右的时间。

希望高手们赐教，能提高效率。
谢谢

[华夏小卒]

WHEN MAX(CASE TEES.EvalEmpType WHEN 4 THEN TER.EvalPoint END) IS NULL 

这个地方看看

[--小F--]

嵌套了几层了 把里面的嵌套用临时表来处理试下

[dawugui]

光看语句看不明白.

最好给出表结构,测试数据,计算方法和正确结果.

[guguda2008]

先把转接的结果存成临时表或者表变量再处理吧。

[icelovey]

[Quote=引用 3 楼 dawugui 的回复:]
光看语句看不明白.

SQL code最好给出表结构,测试数据,计算方法和正确结果.
[/Quote]
对, 最好给出数据, 要什么样的结果...这样看看能不能帮你想个好算法

[baggio328]

create table a (e_id int , evalType smallint, point int)

insert into a values(1, 0, 5)
insert into a values(1, 3, 3)
insert into a values(1, 4, 5)

insert into a values(2, 0, 5)
insert into a values(2, 3, 3)
insert into a values(2, 4, 5)

insert into a values(3, 0, 5)
insert into a values(3, 3, 3)

insert into a values(4, 0, 5)
insert into a values(4, 3, 3)

select * from a

select ISNULL(MAX(CASE evalType WHEN 0 THEN point END) * 0.2
            + MAX(CASE evalType WHEN 3 THEN point END) * 0.4
            + (CASE 
                   WHEN MAX(CASE evalType WHEN 4 THEN point END) IS NULL 
                       THEN MAX(CASE evalType WHEN 3 then point END) * 0.4
                   ELSE MAX(CASE evalType WHEN 4 THEN point END) * 0.4
                END)
            , 0) AS POINT2
from a
group by e_id

算法就是根据id来分组，
evalType 为 4 的数据如果有的话，
计算点数的公式为
evalType = 0 的点数 * 0.2 + evalType = 3 的点数 * 0.4 + evalType = 4 的点数 * 0.4

如果不存在的话
计算点数的公式为
evalType = 0 的点数 * 0.2 + evalType = 3 的点数 * 0.8

[guguda2008]

create table a (e_id int , evalType smallint, point int)

insert into a values(1, 0, 5)
insert into a values(1, 3, 3)
insert into a values(1, 4, 5)

insert into a values(2, 0, 5)
insert into a values(2, 3, 3)
insert into a values(2, 4, 5)

insert into a values(3, 0, 5)
insert into a values(3, 3, 3)

insert into a values(4, 0, 5)
insert into a values(4, 3, 3)

SELECT * FROM A
select T1.E_ID,T1.POINT,T2.POINT,T3.POINT
,T1.POINT*0.2
+T2.POINT*(CASE WHEN T3.E_ID IS NULL THEN 0.8 ELSE 0.4 END)
+ISNULL(T3.POINT*0.4,0) 
from a T1
INNER JOIN A T2 ON T1.e_id=T2.e_id AND T1.EVALTYPE=0 AND T2.EVALTYPE=3
LEFT JOIN A T3 ON T1.e_id=T3.e_id AND T3.EVALTYPE=4


/*
1	5	3	5	4.2
2	5	3	5	4.2
3	5	3	NULL	3.4
4	5	3	NULL	3.4
*/

[guguda2008]

e_id,EVALTYPE加索引，然后看执行计划走没走索引

[ChinaJiaBing]

create table a (e_id int , evalType smallint, point int)

insert into a values(1, 0, 5)
insert into a values(1, 3, 3)
insert into a values(1, 4, 5)

insert into a values(2, 0, 5)
insert into a values(2, 3, 3)
insert into a values(2, 4, 5)

insert into a values(3, 0, 5)
insert into a values(3, 3, 3)

insert into a values(4, 0, 5)
insert into a values(4, 3, 3)

--select e_id ,case when evaltype=4 then  from a
select e_id,point= case when max(evaltype)=4 then 
      sum(case when evaltype=0 then point*0.2 when evaltype=3 then point*0.4 
      when evaltype=4 then point*0.4 end) else 
       sum(case when evaltype=0 then point*0.2 when evaltype=3 then point*0.8
      end) end from a
           group by e_id
go
drop table a


/*
e_id        point
----------- ---------------------------------------
1           4.2
2           4.2
3           3.4
4           3.4

*/

[xman_78tom]

select e_id, max(case evaltype when 0 then point else 0 end)*0.2
+max(case evaltype when 4 then point else 0 end)*0.4
+max(case evaltype when 3 then point else 0 end)*
(case max(evaltype) when 4 then 0.4 else 0.8 end)
from a group by e_id

[baggio328]

烤鸭的方法比较有效，提高了20多秒。多谢。
同时谢谢其他各位