php - 联合查询以获取 SUM 值

Question

总共12个db表(有些表重复)。我必须从特定日期的每个表中获取 SUM(值)。我使用了 UNION 查询，但它返回查询中使用的第一个表的值。剩余的表没有返回任何内容。有人可以帮助我吗？这是我的代码。

$sel = mysql_query("
SELECT 
    SUM(collection_amount) AS cash_total 
FROM 
    collection_entry 
WHERE 
    date='$entered_date' 
    AND collection_type='DC' 
UNION 
SELECT 
    SUM(amt) AS cheque_redeposit_total 
FROM 
    cheque_redeposit 
WHERE 
    redeposited_on1 
    OR redeposited_on2='$entered_date' 
UNION 
SELECT 
    SUM(collection_amount) AS not_cleared_total 
FROM 
    collection_entry 
WHERE 
    cheque_status='not cleared' 
    AND date='$entered_date' 
UNION 
SELECT 
    SUM(collection_amt) AS route_collection_total 
FROM 
    route_collection 
WHERE 
    entered_date='$entered_date' 
UNION 
SELECT 
    SUM(amt) AS return_total 
FROM 
    cheque_return 
WHERE 
    return_date1 OR return_date2 OR return_date3='$entered_date' 
UNION 
SELECT 
    SUM(collection_amount) AS cheque_total 
FROM 
    collection_entry 
WHERE 
    collection_type='CC' 
    AND date='$entered_date' 
UNION 
SELECT 
    SUM(debit2) AS voucher_receipt_total 
FROM 
    voucher_posting 
WHERE 
    receipt_type='R' 
    AND date='$entered_date' 
UNION 
SELECT 
    SUM(credit2) AS voucher_payment_total 
FROM 
    voucher_posting 
WHERE 
    receipt_type='P' 
    AND date='$entered_date' 
UNION 
SELECT 
    SUM(amt) AS others_total 
FROM 
    others_remittance 
WHERE 
    entered_date='$entered_date' 
UNION 
SELECT 
    SUM(amt) AS short_total 
FROM 
    short_remittance 
WHERE 
    entered_date='$entered_date' 
UNION 
SELECT 
    SUM(amount) AS more_paid 
FROM 
    difference 
WHERE 
    entered_date='$entered_date' 
    and paid_type='more' 
UNION 
SELECT 
    SUM(amount) AS unpaid 
FROM 
    difference 
WHERE 
    entered_date='$entered_date' 
    and paid_type='unpaid'");
 while($row=mysql_fetch_array($sel)) 
 {
  $cash_total=$row['cash_total'];
  $cheque_redeposit_total=$row['cheque_redeposit_total'];
  $not_cleared_total=$row['not_cleared_total'];
  $route_collection_total=$row['route_collection_total'];
  $return_total=$row['return_total'];
  $cheque_total=$row['cheque_total'];
  $voucher_receipt_total=$row['voucher_receipt_total'];
  $voucher_payment_total=$row['voucher_payment_total'];
  $others_total=$row['others_total'];
  $short_total=$row['short_total'];
  $more_paid=$row['more_paid'];
  $unpaid=$row['unpaid'];
  $net_total = (($cash_total + $route_collection_total) - $return_total);
 }

Answer 1

UNION 只是将行相互追加。因此，在您的情况下，您只需以行方式获得总和列表。

• [cash_total 值]
• [cheque_redeposit_total 的值]
• [not_cleared_total 的值]
• ...

如果您确实必须将所有数据放在一行中，您可以使用如下内容:

SELECT * FROM
(SELECT SUM(collection_amount) AS cash_total FROM collection_entry WHERE date='$entered_date' AND collection_type='DC') as t1,
(SELECT SUM(amt) AS cheque_redeposit_total FROM cheque_redeposit WHERE redeposited_on1 OR redeposited_on2='$entered_date') AS t2,
(SELECT SUM(collection_amount) AS not_cleared_total FROM collection_entry WHERE cheque_status='not cleared' AND date='$entered_date') AS t3,
(SELECT SUM(collection_amt) AS route_collection_total FROM route_collection WHERE entered_date='$entered_date') AS t4,
...