Mysql子查询问题

发布于 2024-09-25 21:25:11 字数 542 浏览 5 评论 0原文

贷款（列：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”

原文

loans (columns: id, user)
payback (columns: id, installment, scheduleDate)

If a loan is to be paid in 12 installments, then there are 12 payback records with loan id.

I would like to find out what users have a scheduleDate on which the sum of all their paybacks is greater than 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

I'm getting the error:
Unknown column 'ka.user' in 'where clause'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

呆头 2024-10-02 21:25:11

这是否按要求工作？它假设 payback.id 是引用loads.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

Does this work as requested? It assumes, that payback.id is a foreign key referring to loads.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

回复收藏 0 原文

~没有更多了~