php - 将脚本查询简化为单个查询

我有一个脚本，该脚本从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

关于php - 将脚本查询简化为单个查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24755020/