读书人

有哪位高手可以帮小弟我优化下SQL分

发布时间: 2012-02-12 17:16:33 作者: rapoo

有谁可以帮我优化下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

读书人网 >SQL Server

热点推荐