php - mysql错误内部联接

SELECT
  bp.project_id,
  bp.project_name,
  bp.project_costing,
  bp.project_borrower_id,
  bp.member_userid,
  bp.project_staus,
  SUM(pb.payment_amount) as total
FROM borrower_project_master as bp
INNER JOIN payment_invest_master as pb ON bp.project_borrower_id=pb.payment_borrowerid
WHERE (
 (SUM(pb.payment_amount)/bp.project_costing)*100 < 100
  AND bp.project_staus='Y'
)
ORDER BY RAND() LIMIT 0,3

此查询显示错误无效使用组函数。你能帮我解决这个问题吗

最佳答案

只需将JOIN表替换为子查询，子查询计算每个payment_borrowerid

SELECT bp.project_id,bp.project_name,bp.project_costing,bp.project_borrower_id,
       bp.member_userid,bp.project_staus,pb.SUM_payment_amount as total
    FROM  borrower_project_master as bp
    INNER JOIN
    ( select payment_borrowerid,SUM(payment_amount) as SUM_payment_amount
      FROM
      payment_invest_master
      GROUP BY  payment_borrowerid
    )
    as pb ON bp.project_borrower_id=pb.payment_borrowerid
    WHERE (

        (pb.SUM_payment_amount/bp.project_costing)*100 < 100
         AND bp.project_staus='Y'

        )

    ORDER BY RAND() LIMIT 0,3

关于php - mysql错误内部联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19722576/