php - 将 2 个 Mysql 查询组合成 1 个(有效方式)

Question

我有 2 个表:

1. 第一个包含当天(每小时)的数据 - 此表包含需要处理的原始数据。

2. 第二个包含前一天(每天)的数据 - 此表已包含每天计算的值。

我想将其合并到 MySQL 查询中的行，以便我返回以前日期(每天)和当前日期(再次，每天)的数据。

目前我正在使用 2 个 mysql 查询:

//table 1, data for current day
                    $qry1="
            select DATE_FORMAT(completedate,'%Y-%m-%d') as date, SUM(IF(complete = 1,affpayout,0)) as pay, COUNT(*) as leads
            from leads
            where affiliateid={$_SESSION["id"]}  AND completedate>'$date' 
            GROUP BY DATE_FORMAT(completedate,'%Y-%m-%d')";
//table 2, data for previous days, already processsed, we just need to select it
            $qry2="
            select DATE(date) as date, affrevenue as pay, totalleads as leads
            from leadsdays
            GROUP BY DATE(date)";

如何有效地将两者结合起来(速度性能是一个问题)？执行此操作的最佳方法是什么？

Answer 1

尝试UNION。另外，请确保您的表已正确编制索引。看起来您至少需要将 completedate 和 affiliateid 编入索引。虽然不确定两个查询比一个联合查询效率高多少......

$qry = "(select 
    DATE_FORMAT(completedate,'%Y-%m-%d') as `date`, 
    SUM(IF(complete = 1,affpayout,0)) as pay, 
    COUNT(*) as leads
    from leads
    where affiliateid={$_SESSION["id"]}  AND completedate>'$date' 
    GROUP BY DATE_FORMAT(completedate,'%Y-%m-%d')) 
    UNION 
    (select 
    DATE(`date`) as `date`, 
    affrevenue as pay, 
    totalleads as leads
    from leadsdays
    GROUP BY DATE(`date`)) 
    ORDER BY DATE(`date`)";