Mysql subquery problem
- loans (columns: id, u开发者_如何学Goser)
- 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'
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
精彩评论