mariadb 不使用子查询的左连接

vptzau2j 于 2022-11-08 发布在其他

关注(0)|答案(2)|浏览(145)

我得到了下面的输出，我希望输出相同，但没有子查询

。我希望通过删除子查询来细化下面的查询，因为它会影响应用程序的性能。有人能帮助我删除子查询，并使用联接获取所需的数据吗？

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'

我想避免下面这些子查询。
（从候选人反馈WHERE候选人反馈.请求编号= rec.请求编号&&候选人反馈.状态= '已选择'中选择COUNT（候选人反馈.请求编号））作为总选择

mariadb

来源：https://stackoverflow.com/questions/72257940/left-join-without-subqueries

2条答案

按热度按时间

3j86kqsm1#

谢谢朋友们的帮助，我已经想好了怎么做，下面是解决方法，供大家参考。

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

赞(0）回复(0）举报 2022-11-09

ql3eal8s2#

回复：您的子查询：
（从招聘人员提交中选择min（日期与时间），其中招聘人员提交.请求编号= rec.请求编号）
需要INDEX(requestno, dateandtime)。其他子查询也是如此。
回复：Seejith的回答：

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

可替换为

SUM(status = 'Selected')

从而消除了对COALESCE(.., 0)的需要。

赞(0）回复(0）举报 2022-11-09

我来回答

mariadb 不使用子查询的左连接

2条答案

相关问题

热门标签

最新问答