Справка по коррелированным подзапросам в MySQL

Мне нужно найти последнюю строку для каждого идентификатора в моей таблице, где было указано «Да», а затем найти в той же таблице следующий экземпляр этого идентификатора, чтобы убедиться, что запись была «Нет». В настоящее время это прекрасно работает в запросе в сочетании с php foreach, который использует результаты первого запроса в предложении where второго. Набор данных содержит чуть более 3000 строк, и весь процесс ужасно неэффективен, поскольку на его завершение уходит чуть более 4 минут.

Я знаю, что могу сделать это с помощью sql, только если бы у меня был только один критерий Where, но поскольку у меня их два, я, кажется, ударился о стену и ценю любую помощь, которую я могу получить.

Заранее спасибо.

//Get max dispatchID where Available = 'Yes'
    <?php $sql = "SELECT a.memberID, MAX(a.dispatchID) as lastDispatchIDYes
                FROM  members_availability a
                WHERE available = 'Yes'
                GROUP BY memberID";
    $qry = $this->db->query($sql);              
    foreach($qry->result() as $row){
        $sql2 = "SELECT dispatchID, memberID, 
                     FROM members_dispatch WHERE memberID = '".$row->memberID."' AND dispatchID > '".$row->lastDispatchIDYes."' AND available = 'No'ORDER BY dispatchID ASC LIMIT 1";   
        $qry2 = $this->db->query($sql2);
        if($qry2->num_rows() > 0){                  
            echo $row->memberID." switched availability at ".$row->dispatchID."<br />";
        }           
    } ?>

mysql php sql correlated-subquery
person Sheldon Pereira    schedule 20.12.2011    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Это идея для решения вашей проблемы.

select tableNo.*
from
    (SELECT a.memberID, MAX(a.dispatchID) as lastDispatchIDYes
    FROM  members_availability a
    WHERE available = 'Yes'
    GROUP BY memberID) tableYes
    (SELECT a.memberID, a.dispatchID as dispatchIDNo
    FROM  members_availability a
    WHERE available = 'No'
    ) tableNo 
where tableYes.lastDispatchIDYes < tableNo.lastDispatchIDNo AND
      tableYes.memberID = tableNo.memberID

При этом будут получены все строки с доступными = НЕТ, чей идентификатор отправки больше, чем последняя строка, чей доступный был да.

person Zohaib    schedule 20.12.2011
comment
Спасибо, Зохаиб, через 10 минут этот запрос истек, хотя это выглядело так, как я пытался. - person Sheldon Pereira; 20.12.2011
comment
Рассматривали ли вы добавление индексов в столбцы для повышения производительности. В вашем случае может помочь составной индекс по memberId и available. - person Zohaib; 20.12.2011

arrow_upward
0
arrow_downward

Если я понимаю, что вам нужно, я думаю, что этот запрос может помочь:

SELECT `a1`.`memberID`, `a1`.`lastDispatchIDYes`, `b`.`dispatchID`, `b`.`available`
FROM (SELECT `a`.`memberID`, MAX(`a`.`dispatchID`) AS `lastDispatchIDYes`
FROM `members_availability` `a` WHERE `a`.`available` = 'Yes'
GROUP BY `a`.`memberID`) `a1`
INNER JOIN `members_availability` `b` ON `b`.`memberID` = `a1`.`memberID` AND `b`.`dispatchID` = `a1`.`lastDispatchIDYes` + 1;
$qry = $this->db->query($sql);              
foreach($qry->result() as $row) {
    echo $row->memberID . " switched availability at " . $row->lastDispatchIDYes . "<br />";
}

Кроме того, кажется, что может возникнуть проблема с оператором «эхо» в примере кода, упомянутом в вопросе. $row->dispatchID не является допустимым значением в наборе результатов. Я думаю, что это должно быть $row->lastDispatchIDYes. Нет?

РЕДАКТИРОВАТЬ 1: обновил запрос, чтобы запись "Нет" соответствовала любой записи, для которой идентификатор отправки больше, чем идентификатор отправки последней записи со значением "Да". Исходный запрос выше читал только те записи «Нет», для которых идентификатор dispatchID был непосредственно следующим.

SELECT DISTINCT `a1`.`memberID`, `a1`.`lastDispatchIDYes`
FROM (
    SELECT `a`.`memberID`, MAX(`a`.`dispatchID`) AS `lastDispatchIDYes`
    FROM `members_availability` `a`
    WHERE `a`.`available` = 'Yes'
    GROUP BY `a`.`memberID`
    ) `a1`
INNER JOIN `members_availability` `b`
    ON `b`.`memberID` = `a1`.`memberID`
    AND `b`.`available` = 'No'
WHERE `b`.`dispatchID` > `a1`.`lastDispatchIDYes`;

Надеюсь это поможет!

person Abhay    schedule 20.12.2011
comment
Спасибо, Абхай, это сработало хорошо и очень быстро, но вернуло только 3 строки, их должно быть около 1200, возможно, группировка или объединение неверны, я продолжу работать над этим, и спасибо за подсказку в операторе эха. - person Sheldon Pereira; 20.12.2011
comment
@SheldonPereira, приятно знать, что это сработало. Для запроса нет LIMIT, поэтому я думаю, что может быть только 3 строки, соответствующие желаемым критериям, то есть максимальный идентификатор отправки с «Да», за которым следует «Нет» для следующего идентификатора отправки. Но я не могу комментировать это, потому что не могу смотреть на данные. Пожалуйста, не стесняйтесь, дайте мне знать, если вам понадобится помощь :-) - person Abhay; 20.12.2011
comment
Таким образом, проблема заключалась в критериях JOIN, где вы использовали b.dispatchID = a1.lastDispatchIDYes + 1. На самом деле мне нужно было b.dispatchID › a.lastDispatchIDYes.... С этим исправлением запрос выполняется, но довольно медленно, я работаю над это, хорошая база для начала, спасибо. - person Sheldon Pereira; 20.12.2011
comment
@SheldonPereira, если запись «Нет» не обязательно должна быть следующей за последней «Да», может быть другой способ написать запрос. Замена = на › может замедлить запрос, как вы упомянули. Я постараюсь внести исправление в свой ответ. - person Abhay; 20.12.2011
comment
Привет, @SheldonPereira, ты смог это проверить? Как прошло? - person Abhay; 23.12.2011