我有这个查询,它工作:
SELECT DISTINCT sid, name, last_name, tuition
FROM students
WHERE EXISTS (SELECT * FROM payments
WHERE payments.forMonth = 'May'
AND students.sid = payments.sid)
然而,当我试图从支付表中添加几列时,查询失败了:
SELECT DISTINCT students.sid, students.name, students.last_name,
students.tuition, payments.amount, payments.forMonth
FROM students payments
WHERE EXISTS (
SELECT *
FROM payments
WHERE payments.forMonth = 'Apr'
AND students.sid = payments.sid)
我在查询的上半部分尝试了一个内部连接,但是产生了错误的结果。有办法做到这一点吗?
(使用EXISTS条件的原因是,我将在过滤器表单上设置一个开关,显示已支付或未支付的款项。)
外部查询使用内部连接并将其forMonth信息传递给子查询。可以这样想,如果exist语句不知道外部查询查询的是哪个月份,它怎么可能有用呢?
SELECT DISTINCT students.sid, students.name, students.last_name,
students.tuition, payments.amount, payments.forMonth
FROM students
INNER JOIN payments ON students.sid = payments.sid
WHERE EXISTS (
SELECT *
FROM payments p2
WHERE p2.forMonth = payments.forMonth )
And payments.forMonth = 'Apr'