沒有子查詢的左連接

我得到了下面的輸出，我想要同樣的輸出，但沒有子查詢。我希望通過洗掉子查詢來優化下面的查詢，因為它會影回應用程式的性能。誰能幫我洗掉子查詢并使用連接獲取所需的資料。

SELECT DISTINCT rec.requestno as requestno
    , (SELECT min(dateandtime) 
       from recruitersubmission 
       where recruitersubmission.requestno = rec.requestno) as firstsubmsion
    , (SELECT max(dateandtime) 
       from recruitersubmission 
       where recruitersubmission.requestno = rec.requestno) as lastsubmission
    , cndinterview.dateandtime as  candidatefedtime
    , cndinterview.dateandtime AS recruitertime
    , CONCAT(availabledate,' ', availabletime) AS candidateavaibledatetime
    , cndfeedback.status AS status
    , cndfeedback.offereddate AS offereddate
    , cnd.status AS onboard
    , (SELECT COUNT(recruitersubmission.requestno)
       FROM recruitersubmission 
       WHERE recruitersubmission.requestno=rec.requestno) AS totalSub
    , (select COUNT(candidatefeedback.requestno) 
       from candidatefeedback 
       WHERE candidatefeedback.requestno = rec.requestno 
          && candidatefeedback.status = 'Selected' ) as totalsel
    , (select COUNT( candidatefeedback.requestno) 
       from candidatefeedback 
       WHERE candidatefeedback.requestno = rec.requestno 
          && cndfeedback.status = 'Rejected' ) as totalrej
    , (select COUNT(candidatefeedbkonboard.requestno) 
       from candidatefeedbkonboard 
       WHERE candidatefeedbkonboard.requestno = rec.requestno 
          && cnd.status = 'Drop' ) as totaldrop
    , (select COUNT(candidatefeedbkonboard.requestno) 
       from candidatefeedbkonboard 
       WHERE candidatefeedbkonboard.requestno = rec.requestno 
          && candidatefeedbkonboard.status = 'Onboarded' ) as totalonboard
from recruitersubmission AS rec 
LEFT JOIN candidatefeedbkonboard AS cnd 
    ON rec.requestno=cnd.requestno 
LEFT JOIN candidatefeedback AS cndfeedback 
    ON rec.requestno=cndfeedback.requestno 
LEFT JOIN candidatesinterview AS cndinterview 
    ON rec.requestno=cndinterview.requestno
where rec.clientname = '$client' 
   && rec.requestno != ''  
   && rec.country = '$location' 
   && date(rec.dateandtime) between '$fromdate'

我想避免這些下面的子查詢。

（從 Candidatefeedback WHERE Candidatefeedback.requestno = rec.requestno && Candidatefeedback.status = 'Selected' 中選擇 COUNT(candidatefeedback.requestno) 作為 totalsel

uj5u.com熱心網友回復：

謝謝朋友的幫助。我想出了怎么做。以下是供您參考的解決方案。

SELECT
    rec.requestno,
    COALESCE(totalsub, 0) AS totalsub,
    COALESCE(totalsel, 0) AS totalsel,
    COALESCE(totalrej, 0) AS totalrej
FROM recruitersubmission rec
LEFT JOIN (
    SELECT requestno, COUNT(*) AS totalsub
    FROM recruitersubmission
    GROUP BY requestno
) recruitersubmission  ON recruitersubmission.requestno = rec.requestno
LEFT JOIN (
    SELECT  requestno,count(if(status = 'Selected',1,Null)) AS totalsel,count(if(status = 'Rejected',1,Null)) as totalrej
    FROM candidatefeedback 
    GROUP BY requestno
) candidatefeedback  ON candidatefeedback.requestno = rec.requestno


GROUP by rec.requestno Limit 0,25

uj5u.com熱心網友回復：

回復：您的子查詢：

（從招聘人員提交中選擇分鐘（日期和時間），其中招聘人員提交.requestno = rec.requestno）

需要INDEX(requestno, dateandtime)。對于其他子查詢也是如此。

回復：Seejith 的回答：

count(if(status = 'Selected',1,Null))

可以換成

SUM(status = 'Selected')

從而消除了對COALESCE(.., 0).

標籤：mysql sql 加入 玛丽亚数据库 子查询

上一篇：如何根據十進制列連接兩個表？

下一篇：SQLAlchemy-限制joinedloaded結果