mysql - MySQL子查询问题

贷款（列：id，用户）
投资回报（列：id，分期付款，scheduleDate）


如果要分12期偿还贷款，那么将有12条带有贷款ID的偿还记录。

我想找出哪些用户的scheduleDate的所有回报之和大于2000。

SELECT user
FROM {loans} ka
WHERE
(
   SELECT MAX(inst) FROM
   (
        SELECT SUM(installment) AS inst
        FROM {payback}
        WHERE id IN
        (
            SELECT id
            FROM {loans}
            WHERE user = ka.user
        )
   GROUP BY scheduleDate
   ) as t1 LIMIT 0,1
) > 2000

我收到错误消息：
“ where子句”中的未知列“ ka.user”

最佳答案

是否可以按要求工作？它假定payback.id是引用load.id的外键。

SELECT
  user, scheduleDate, sumInstallments
FROM
(
    SELECT
      l.user, pb.scheduleDate, SUM(pb.installment) AS sumInstallments
    FROM
      payback AS pb
    JOIN
      loans AS l
    ON
      l.id = pb.id
    GROUP BY
      l.user, pb.scheduleDate
) AS tempId
WHERE
  sumInstallments > 2000