我有这样的代码:
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);
因此，$ affiliation _sales['total'] = 128000，而实际上应该是32000，因为有多个affiliation ate_values和affiliation ate_orders_id。affilaite_values有些具有相同的值，因此它们不能区分。Affilaite_orders_id具有所有唯一值，但有多行，并且需要不同。然后，必须根据不同的associate_orders_id行对associate_values求和，以获得准确的和。
我试图得到所有的附属值的总和基于多少不同的附属值在表中
根据你的更新，我认为这将得到你想要的。您需要使用子查询
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
这就引出了一个更大的问题。表中是否缺少连接条件?通常你不应该在查询中返回重复的日期，所以我建议首先仔细检查你的查询是否正确连接。