我有两个表
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