mysql - 将两个查询合并为一个而不循环 mysql

Question

如何在单个查询中加入此查询是否有助于将这两个查询合并为一个而不循环，

$today_date = mktime(0, 0, 0, $mon, $day-1, $year);

SELECT *  FROM (`lead_follow_up`) LEFT JOIN `leads` ON `leads`.`id` = `lead_follow_up`.`lead_id` WHERE `date` <=  $today_date GROUP BY `lead_follow_up`.`lead_id` ORDER BY `lead_follow_up`.`date` DESC
from the above query i get array $previou



$previou=    Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [lead_id] => 75943
            [date] => 1438930800
            [updated_on] => 1438884890
        )

    [1] => stdClass Object
        (
            [id] => 2
            [lead_id] => 75943
            [date] => 1416459600
            [updated_on] => 1415901523
        ),
     [2] => stdClass Object
        (
            [id] => 3
            [lead_id] => 75943
            [date] => 1416459600
            [updated_on] => 1415901523
        ),....etc

);

foreach($previou as $key => $p):
    $q = "SELECT `id` FROM (`lead_follow_up`) WHERE `lead_id` = '".$p->id."' AND `date` > '".$p->date."' ORDER BY `updated_on` DESC ";
    if(!$this->db->query($q)){
        $previouData[$key] = $p;
        $pCount++;
    }
endforeach;

如何在单个查询中加入此查询是否有助于将这两个查询合并为一个而不循环，

Answer 1

您的查询没有多大意义。首先，您的第一个查询有一个 GROUP BY lead_follow_up.lead_id 但没有聚合函数。因此在 MySQL 中，将为 lead_id 的每个值返回一行(它返回的行未定义)。

但是您的示例数据数组每个 lead_id 有多行，因此不可能来自查询。

您还在 LEFT OUTER JOINing 潜在客户表中，但拥有与潜在客户无关的 lead_follow_up 似乎没有意义。因此，您也可以使用 INNER JOIN。

我将假设您想要的是一个潜在客户/lead_follow_ups 列表，并为每个人提供特定跟进之后的所有跟进。那会给你这样的东西(因为我不知道你的表结构而做出大量假设):-

SELECT leads.id AS lead_id,
        lead_follow_up.id
        lead_follow_up.`date`,
        lead_follow_up.updated_on,
        COUNT(lead_follow_up_future.id) AS future_lead_count
FROM leads 
INNER JOIN lead_follow_up ON leads.id = lead_follow_up.lead_id 
LEFT OUTER JOIN lead_follow_up AS lead_follow_up_future ON leads.id = lead_follow_up.lead_id AND lead_follow_up_future.`date` > lead_follow_up.`date` 
WHERE lead_follow_up.`date` <=  $today_date 
GROUP BY leads.id AS lead_id,
        lead_follow_up.id
        lead_follow_up.`date`,
        lead_follow_up.updated_on
ORDER BY lead_follow_up.date DESC