将带有exists的查询转换为join

fruv7luv  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(376)

我想以这样一种方式转换这个查询,使它不具有任何“exists”并且只使用简单联接。

select  t.Teacher_id, t.Teacher_name, a.marks, a.grade_ID
from Grades a
left join students s on a.student_ID = s.student_ID
left join Teachers t on t.Teacher_ID = s.Teacher_ID
where 1=1 and t.Teacher_id = 1807600
and   exists(
select p.Payment_ID from payments p 
inner join lookups l on (l.lookup_id = p.status_id and l.lookup_key in ('condition1','condition2'))  
where p.student_ID = a.student_ID
)

我试过这样的方法:

select  t.Teacher_id, t.Teacher_name, a.marks, a.grade_ID
from Grades a
left join students s on a.student_ID = s.student_ID
left join Teachers t on t.Teacher_ID = s.Teacher_ID
inner join payments p on p.student_ID = a.student_ID
inner join lookups l on (l.lookup_id = p.status_id and l.lookup_key in ('condition1','condition2')) 
where 1=1 and t.Teacher_id = 1807600

但我没有得到正确的结果。你能帮忙吗。谢谢。

vlf7wbxs

vlf7wbxs1#

我想您可以使用groupby,但这限制了脚本的可维护性,因为不使用exists()。

select  t.Teacher_id, t.Teacher_name, a.marks, a.grade_ID
from Grades a
left join students s on a.student_ID = s.student_ID
left join Teachers t on t.Teacher_ID = s.Teacher_ID
inner join payments p on p.student_ID = a.student_ID
inner join lookups l on (l.lookup_id = p.status_id and l.lookup_key in ('condition1','condition2')) 
where t.Teacher_id = 1807600
group by  t.Teacher_id, t.Teacher_name, a.marks, a.grade_ID

相关问题