php - 返回组中的所有行

有没有办法让所有的行在一个组中使用？
如您所料，下面的查询每custref只返回一行。但是如果totalamount大于0，我希望它返回组中的所有行。否则没有。

$sql = "SELECT
    debtors.custref,
    debtors.amount,
    users.bnkSort,
    users.bnkAccNum,
    users.bnkAccName,
    SUM(debtors.amount) AS totalamount
    FROM debtors
    LEFT JOIN users ON debtors.custref=users.custref
    WHERE
    users.bank = 'Yes'
    GROUP BY debtors.custref
";

最佳答案

查询中有多个列被组中的行中不同的group by--列失效。此外，where子句正在撤消left join。所以，这基本上是你的问题：

SELECT d.custref, SUM(d.amount) AS totalamount
FROM debtors d JOIN
     users u
     ON d.custref = u.custref
WHERE u.bank = 'Yes'
GROUP BY d.custref;

要获取所有字段，可以将其用作子查询：

SELECT d.*, u.*
FROM (SELECT d.custref, SUM(d.amount) AS totalamount
      FROM debtors d JOIN
           users u
           ON d.custref = u.custref
      WHERE u.bank = 'Yes'
      GROUP BY d.custref
      HAVING totalamount > 0
     ) dc JOIN
     debtors d
     ON dc.custref = d.custref JOIN
     users u
WHERE u.bank = 'Yes';

 ON dc.custref = u.custref

关于php - 返回组中的所有行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29156620/