gpt4 book ai didi

mysql - 在 mysql 查询 EXISTS 中给出错误结果

转载 作者:行者123 更新时间:2023-11-29 13:49:02 25 4
gpt4 key购买 nike

我已将以下查询从 mssql 迁移到 mysql。

SELECT employee.ecode, fname, mname, lname
FROM employee
WHERE employee.cmp_dol IS NULL
AND employee.ecode IN
(SELECT leave_log.ecode
FROM leave_log
WHERE (l_acc_code = 11 OR l_acc_code = 21 OR l_acc_code = 31 OR l_acc_code = 41 OR l_acc_code = 51 OR l_acc_code = 61 )
GROUP BY leave_log.ecode, l_date, l_acc_code
HAVING SUM(l_value) <> 0)
AND employee.ecode IN ((SELECT ecode
FROM emp_supervisor
WHERE sup_id = @ecode
AND emp_sup_dol IS NULL)
UNION
(SELECT ecode
FROM emp_project
WHERE proj_emp_dol IS NULL
AND pid IN (SELECT pid
FROM proj_supervisor
WHERE proj_sup_dol IS NULL
AND sup_id =@ecode
AND pid IN (SELECT pid
FROM projects
WHERE p_end_date IS NULL
AND leave_flag=1))))
ORDER BY fname

查询中的 IN 语句会花费无尽的时间。但是当我将其替换为存在时,它会给出不同的输出。

任何帮助将不胜感激。

最佳答案

MySQL 在使用子查询优化 WHERE IN 方面非常糟糕,请使用 JOIN 代替:

SELECT employee.ecode, fname, mname, lname
FROM employee
JOIN (SELECT DISTINCT ecode
FROM emp_supervisor
WHERE sup_id = @ecode
AND emp_sup_dol IS NULL
UNION DISTINCT
SELECT DISTINCT ecode
FROM emp_project
WHERE proj_emp_dol IS NULL
AND pid IN (SELECT pid
FROM proj_supervisor
WHERE proj_sup_dol IS NULL
AND sup_id =@ecode
AND pid IN (SELECT pid
FROM projects
WHERE p_end_date IS NULL
AND leave_flag=1))) e
ON employee.ecode = e.ecode
JOIN (SELECT DISTINCT ecode
FROM (SELECT leave_log.ecode
FROM leave_log
WHERE (l_acc_code IN (11, 21, 31, 41, 51, 61)
GROUP BY leave_log.ecode, l_date, l_acc_code
HAVING SUM(l_value) <> 0) x) l
ON employee.ecode = l.ecode
WHERE employee.cmp_dol IS NULL
ORDER BY fname

如果子查询有重复项,您需要在子查询中使用 DISTINCT 以避免结果中的行相乘。

关于mysql - 在 mysql 查询 EXISTS 中给出错误结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16976987/

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