mysql - 我通过嵌套使用查询并希望加入操作

Question

由于数据超过 2000，此代码需要更多时间或崩溃。请建议我新的查询。

此查询用于多级。我的数据库每天都在增加。如果我有 2M 数据....thannnn...

帮我。

SELECT DISTINCT emp_id 
FROM emp 
WHERE (referral IN (SELECT emp_id FROM emp 
                    WHERE referral IN (SELECT emp_id FROM emp  
                                       WHERE referral IN (SELECT emp_id FROM emp 
                                                          WHERE referral ='2') 
                                      )
                   ) 
   OR referral IN (SELECT emp_id FROM emp 
                   WHERE referral IN (SELECT emp_id FROM emp 
                                      WHERE referral = '2')
                  ) 
   OR referral IN (SELECT emp_id FROM emp WHERE referral ='2'))

我正在使用 php 和 mysqli。

标签： mysql

Answer 1

我的意思是下一个查询可以解决问题：

SELECT DISTINCT emp_id 
FROM emp 
LEFT JOIN emp as ref1 ON emp.referral = ref1.emp_id
LEFT JOIN emp as ref2 ON ref1.referral = ref2.emp_id
LEFT JOIN emp as ref3 ON ref2.referral = ref3.emp_id
WHERE 
    ref1.referral ='2' OR
    ref2.referral ='2' OR
    ref3.referral ='2';