php - 如何将这个多计数循环编写为单个查询？

Question

我真的很困惑，我尝试过使用:

sum(当 date_format(from_unixtime(l.date_updated), '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') 然后 1否则 0 结束) AS day0_leads,

在我的查询中，它没有按预期工作，所以我最终使用了这个:

    <?php

    $total_days = '14';     

    for ($i = $total_days - 1; $i >= 0; $i--)
    {
        $day = strtotime('-'.$i.' days');
        $day_string = date('n/j', $day);

        $leads = mysql_result(mysql_query("select count(*) from `leads` where date_format(from_unixtime(`date_updated`), '%m-%d-%Y') = date_format(from_unixtime($day), '%m-%d-%Y')"), 0);
        $assigns = mysql_result(mysql_query("select count(*) from `assigns` where date_format(from_unixtime(`date_assigned`), '%m-%d-%Y') = date_format(from_unixtime($day), '%m-%d-%Y') and `id_dealer` not in (1,2,3)"), 0);

        echo "['$day_string', $leads, $assigns]";

        if ($i > 0)
            echo ',';
    }

    ?>

这使得页面加载速度变慢，显然是由于不必要的查询。将其编写为单个查询并输出结果的正确方法是什么？就像我说的，我尝试过使用 then else 求和，但它没有产生正确的数字。

任何帮助将不胜感激。

Answer 1

这是我的解决方案:

    $total_days = '14';

    // get leads count array

    $sql = mysql_query("select count(*) as `count`, `date_updated` 
                        from `leads` 
                        where date_format(from_unixtime(`date_updated`), '%Y-%m-%d') >= date_format(now() - interval $total_days day, '%Y-%m-%d')
                        group by date(from_unixtime(`date_updated`));") or die(mysql_error());

    $leads_count = array();

    while ($row = mysql_fetch_assoc($sql))
        $leads_count[date('n/j', $row['date_updated'])] = $row['count'];

    // get assigns count array

    $sql = mysql_query("select count(*) as `count`, `date_assigned` 
                        from `assigns` 
                        where date_format(from_unixtime(`date_assigned`), '%Y-%m-%d') >= date_format(now() - interval $total_days day, '%Y-%m-%d') and `id_dealer` not in (1,2,3,4)
                        group by date(from_unixtime(`date_assigned`));") or die(mysql_error());

    $assigns_count = array();

    while ($row = mysql_fetch_assoc($sql))
        $assigns_count[date('n/j', $row['date_assigned'])] = $row['count'];

    for ($i = $total_days - 1; $i >= 0; $i--)
    {
        $day = strtotime('-'.$i.' days');
        $day_string = date('n/j', $day);

        $leads = ((empty($leads_count[$day_string])) ? '0' : $leads_count[$day_string]);
        $assigns = ((empty($assigns_count[$day_string])) ? '0' : $assigns_count[$day_string]);

        echo "['$day_string', $leads, $assigns]";

        if ($i > 0)
            echo ',';
    }