sql - 在选择查询上两次调用相同函数的替代方法

Question

我有一个视图的选择查询，就像

SELECT
    c.id,
    c.name,
    p.name   AS person_name,
    fetch_selected_student_of_class(c.id) as student,
    fetch_selected_section(fetch_selected_student_of_class(c.id), c.id) as section
    FROM
    class   c
    left JOIN persons                  p ON c.room_id = p.id

这里函数fetch_selected_student_of_class重复了两次。所以该函数将被执行两次。有没有办法避免这种情况？

标签： sqloracle

Answer 1

您可以使用子查询：

SELECT c.id, c.name, p.name   AS person_name, c.student,
       fetch_selected_section(c.student, c.id) as section
FROM (SELECT c.*, fetch_selected_student_of_class(c.id) as student
      FROM class c
     ) c LEFT JOIN
     persons p
     ON c.room_id = p.id