日期:2014-05-18  浏览次数:20477 次

有谁可以帮我优化下SQL,分不够可以加
我有两个表
TABLE   SKILL{
          skillId   varchar(20);
          gradeId   varchar(20);
          empId       varchar(20);
}
TABLE   GRADE{
          gradeId   varchar(20);
          gradeValue   int(4);
}

现在我想查询员工,该员工(可能多个)的技能(skillId)A> =3(gradeValue)并且技能(skillId)B> =4(gradeValue)
现在我想到的SQL语句只有
第一:
select   s.empId
from   SKILL   s,   GRADE   g
where   s.skillId= 'A '   and   g.gradeValue> =3   and   s.gradeId=g.gradeId   and   s.empId   in(select   s.empId
from   SKILL   s,   GRADE   g
where   s.skillId= 'B '   and   g.gradeValue> =4   and   s.gradeId=g.gradeId)

第二:
select   s.empId
from   SKILL   s,   GRADE   g
where   s.skillId= 'A '   and   g.gradeValue> =3   and   s.gradeId=g.gradeId  
intersect
select   s.empId
from   SKILL   s,   GRADE   g
where   s.skillId= 'B '   and   g.gradeValue> =4   and   s.gradeId=g.gradeId

不知可不可以再优化一下,不然这样的效率可能会太低了,高手出来帮下忙,觉得分不够可以再加,谢谢。在线等

------解决方案--------------------
select s.empId
from SKILL s, GRADE g
where s.skillId= 'A ' and g.gradeValue> =3 and s.gradeId=g.gradeId and
exists (select s.empId
from SKILL s, GRADE g
where s.skillId= 'B ' and g.gradeValue> =4 and s.gradeId=g.gradeId)
------解决方案--------------------
select enpId from
(
select *
from SKILL s, GRADE g
where s.skillId= 'A ' and g.gradeValue> =3 and s.gradeId=g.gradeId
)A,
(
select * from SKILL s, GRADE g
where s.skillId= 'B ' and g.gradeValue> =4 and s.gradeId=g.gradeId
)B
where A.empId = B.empId