如何改写MySQL查询实现未归档运单发票按公司分组求和？

直接替换原SQL语句即可实现按company_name分组并对amount求和的需求，同时保留只获取未归档运单发票的逻辑：

global $wpdb;
$table_name1 = $wpdb->prefix . 'tms_invoices';
$table_name2 = $wpdb->prefix . 'tms_loads';
/**
* show grouped invoices (sum amount) of loads that are not archived
*/
$rows = $wpdb->get_results("
    SELECT $table_name1.load_id, $table_name1.company_name, SUM($table_name1.amount) AS amount
    FROM $table_name1 
    LEFT JOIN $table_name2 ON $table_name1.load_id = $table_name2.id 
    WHERE $table_name2.is_archived = '0'
    GROUP BY $table_name1.load_id, $table_name1.company_name
", ARRAY_A);
return $rows;

关键修改说明 #

• 替换SELECT $table_name1.*为明确字段：load_id、company_name，以及SUM(amount) AS amount来计算分组后的金额总和
• 添加GROUP BY $table_name1.load_id, $table_name1.company_name，因为同一load_id下不同company_name需要单独分组统计
• 保留原有的LEFT JOIN和WHERE条件，确保只关联未归档（is_archived = '0'）的运单

执行后返回的结果会和你期望的格式一致：

load_id     company_name      amount
  11          company1         70
  11          company2         40

内容的提问来源于stack exchange，提问作者Ravinder singh