我该如何简化这个mysql查询？

I have these 4 queries repeated 3 times for each agent. Is there anyway to simplify/combine these queries? I don't mind using a while loop for the sums. The only thing that changes are the dates.

我为每个代理重复了这4次查询3次。无论如何要简化/组合这些查询?我不介意使用while循环来计算总和。唯一改变的是日期。

$john_week_total  = mysql_result(mysql_query("SELECT SUM(tp) FROM info WHERE type='life' AND date >= '$monday' AND rvp ='john smith'"),0);
$john_month_total = mysql_result(mysql_query("SELECT SUM(tp) FROM info WHERE type='life' AND date >= '$this_month' AND rvp ='john smith'"),0);
$john_year_total  = mysql_result(mysql_query("SELECT SUM(tp) FROM info WHERE type='life' AND date >= '$this_year' AND rvp ='john smith'"),0);
$john_total       = mysql_result(mysql_query("SELECT SUM(tp) FROM info WHERE type='life' AND rvp ='john smith'"),0);

2 个解决方案

#1

You can have multiple SUM aggregators in the field list

您可以在字段列表中包含多个SUM聚合器

SELECT
    SUM(IF(date >= '$monday' AND rvp = 'john smith'), tp, 0) AS john_week_total
    SUM(IF(date >= '$this_month' AND rvp = 'john smith'), tp, 0) AS john_month_totalo
    -- etc.
FROM
    info
WHERE
    type = 'life'

Your code is vulnerable to injection. You should use properly parameterized queries with PDO or mysqli

您的代码易受注入攻击。您应该使用PDO或mysqli正确参数化查询

#2

Does a query of this sort help you?

这种查询对您有帮助吗?

select
   sum(case when date >= '$monday' then tp else 0 end) as weektotal,
   sum(case when date >= '$this_month' then tp else 0 end) as monthtotal,
   sum(case when date >= '$this_year' then tp else 0 end) as yeartotal,
   sum(tp) as alltotal
from info
where type = 'life'
and rvp = 'john smith'

#1