我有此查询,它可以工作:
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信息传递给子查询。这样想,如果不知道外部查询在哪个月份运行,则existing语句将如何有用。
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'
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句