php - 基于另一个字段获取一个字段的不同 mysql 结果

Question

我有这个代码:

select count(distinct affiliate_orders_id) as count
, sum(affiliate_value) as total
, sum(affiliate_payment) as payment 
from " . TABLE_AFFILIATE_SALES . " a 
    left join " . TABLE_ORDERS . " o on (a.affiliate_orders_id = o.orders_id)
where a.affiliate_orders_id = o.orders_id 
and o.orders_status >= " . AFFILIATE_PAYMENT_ORDER_MIN_STATUS . "
        ";

  $affiliate_sales_query= tep_db_query($affiliate_sales_raw);
  $affiliate_sales= tep_db_fetch_array($affiliate_sales_query);

因此，$affiliate_sales['total'] = 128000，而实际上它应该是 32000，因为有多个 affiliate_values 和 affiliate_orders_id。 affilaite_values 有些具有相同的值，因此它们不能不同。 affilaite_orders_id 具有所有唯一值，但有多行并且需要不同。然后 affiliate_values 必须根据 affiliate_orders_id 的不同行进行求和以获得准确的总和。

我正在尝试根据表中有多少不同的 affiliate_orders_id 获取所有 affiliate_values 的总和。

Answer 1

根据您的更新，我认为这将为您提供所需的信息。你需要使用子查询

SELECT COUNT(a) COUNT, SUM(av) total, SUM(ap) aptotal
FROM (
    SELECT affiliate_orders_id a, affiliate_value av, SUM(affiliate_payment) AS ap
from " . TABLE_AFFILIATE_SALES . " a 
    left join " . TABLE_ORDERS . " o on (a.affiliate_orders_id = o.orders_id
    GROUP BY affiliate_orders_id, affiliate_value)
where a.affiliate_orders_id = o.orders_id 
and o.orders_status >= " . AFFILIATE_PAYMENT_ORDER_MIN_STATUS . ") a

现在这引出了一个更大的问题。您是否缺少表中的连接条件？通常，您不应在查询中返回重复日期，因此我建议您先仔细检查您的查询是否正确加入。