组合两个 MySQL 查询 - Combining two MySQL queries?

Combining two MySQL queries?

本文关键字：两个 MySQL 查询组合 | 更新日期: 2023-09-27

$query1 = 'select DATE_FORMAT(time, "%Y-%m-%d") as date,
        count(*) as phone_TotalPerDay
        from numrequest
        where id_usr = "'.$id_user.'"
        AND id_re ="'.$id_re.'"
        AND request="ph"
        group by id_usr, year(time), DAYOFYEAR(time)
        order by year(time) DESC, DAYOFYEAR(time) DESC';  

$query2 = 'select DATE_FORMAT(time, "%Y-%m-%d") as date,
        count(*) as fax_TotalPerDay
        from numrequest
        where id_usr = "'.$id_user.'"
        AND id_re ="'.$id_re.'"
        AND request="fx"
        group by id_usr, year(time), DAYOFYEAR(time)
        order by year(time) DESC, DAYOFYEAR(time) DESC';

有没有办法将这两个查询结合起来。我真的需要它们在一个资源中。目前，每个都显示每天的号码请求数。我想将它们组合在一起，以便每个date都有phone_TotalPerDay和fax_TotalPerDay。

您可以使用条件和，例如

$query1 = 'select DATE_FORMAT(time, "%Y-%m-%d") as date,
    sum(if(request = "ph", 1, 0)) phone_TotalPerDay,
    sum(if(request = "fx", 1, 0)) fax_TotalPerDay
    from numrequest
    where id_usr = "'.$id_user.'"
    AND id_re ="'.$id_re.'"
    group by id_usr, year(time), DAYOFYEAR(time)
    order by year(time) DESC, DAYOFYEAR(time) DESC';

如果请求类型匹配，则 if 语句返回 1，否则返回 0，因此对这些语句求和可以有效地计算与此条件匹配的行，并且我已经从 where 子句中删除了请求条件。