gpt4 book ai didi

MySQL 避免 WHERE/AND 中的子查询重复

转载 作者:行者123 更新时间:2023-11-30 01:19:08 30 4
gpt4 key购买 nike

我有一个 SQL 查询,我在 WHERE 子句中使用子查询。然后我需要再次使用相同的子查询将其与不同的列进行比较。

我假设没有办法在子查询之外访问“emp_education_list li”?

我猜子查询重复是多余的。坦率地说,我对 SQL 有点生疏。

SELECT e.fname, e.lname
FROM employee e, emp_education_list l
WHERE
e.skillsID = l.skillsID
AND
(
SELECT li.educationID
FROM emp_education_list li, job j
WHERE j.skillsID = li.skillsID
AND j.jobID = 1001
) = l.educationID
AND
(
SELECT li.edu_level
FROM emp_education_list li, job j
WHERE j.skillsID = li.skillsID
AND j.jobID = 1001
) < l.edu_level
;

最佳答案

尝试一下,

SELECT  e.fname, e.lname
FROM employee e
INNER JOIN emp_education_list l
ON e.skillsID = l.skillsID
INNER JOIN
(
SELECT li.educationID, li.edu_level
FROM emp_education_list li
INNER JOIN job j
ON j.skillsID = li.skillsID
WHERE j.jobID = 1001
) x ON l.educationID = x.educationID
WHERE x.edu_level < l.edu_level

关于MySQL 避免 WHERE/AND 中的子查询重复,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18756063/

30 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com