将脚本查询简化为单个查询 - Simplifying script query to single query

Simplifying script query to single query

本文关键字：查询单个脚本 | 更新日期: 2023-09-27

我有一个脚本，它从MySQL数据库中选择不同的日期，并在结果中计算贷方和借方。

查看PHP脚本

$query = "SELECT DISTINCT date FROM export WHERE exported = '0'";
$result = $mysqli->query($query);
while($row = $result->fetch_object()){
  $res_sub = $mysqli->query("SELECT SUM(total) AS total FROM export WHERE date = '{$row->date}' AND credit = 'credit');
  $row_sub = $res_sub->fetch_object();
  $creditTot = $row_sub->total;
  $res_sub = $mysqli->query("SELECT SUM(total) AS total FROM export WHERE date = '{$row->date}' AND credit = 'debit');
  $row_sub = $res_sub->fetch_object();
  $debitTot = $row_sub->total;
  echo "Date {$row->date}: credit > {$creditTot} debit > {$debitTot}<br>";
}

但是现在我需要两个子查询，当第一个查询返回100行时，需要100 +(2 * 100)= 300个查询来计算结果。

我很确定这可以用一种更好的方式完成，也许在一个查询中!谁知道答案?

SELECT date, 
       SUM(IF(credit = 'credit', total, 0)) AS credit_total,
       SUM(IF(credit = 'debit', total, 0)) AS debit_total
FROM export
WHERE exported = 0
GROUP BY date