将子查询转换为联接

Question

我有三个表格，即章节、课程和问题，用于显示课程的问题。下面给出表结构供参考。

表“public.chapters”

  Column   |          Type          |                           Modifiers                           
-----------+------------------------+---------------------------------------------------------------
 id        | integer                | not null default nextval('chapters_chapter_id_seq'::regclass)
 chapter   | character varying(255) | not null
 course_id | integer                | 
 published | boolean                | default false

表“public.lessons”

   Column   |          Type          |                          Modifiers                          
------------+------------------------+-------------------------------------------------------------
 id         | integer                | not null default nextval('lessons_lesson_id_seq'::regclass)
 lesson     | character varying(255) | not null
 chapter_id | integer                | 
 published  | boolean                | default false

表“public.questions”

  Column   |          Type          |                            Modifiers                            
-----------+------------------------+-----------------------------------------------------------------
 id        | integer                | not null default nextval('questions_question_id_seq'::regclass)
 template  | character varying(255) | not null
 wording   | character varying(255) | not null
 lesson_id | integer                | not null

现在，我需要查找一章中的问题数量。所以，我使用以下查询。

select sum( num_of_questions ) as num_of_questions, 
       chapter_id 
 from ( select chapters.id as chapter_id,
               lesson_id, 
               count(*) as num_of_questions 
          from questions 
          JOIN lessons ON lessons.id = questions.lesson_id 
          JOIN chapters ON lessons.chapter_id = chapters.id 
      GROUP BY lesson_id, chapters.id  
      ORDER BY lesson_id, chapters.id) as foo 
group by  chapter_id;

如何，我可以将此查询转换为使用联接而不是子查询吗？

Answer 1

使用：

  SELECT c.id as chapter_id,
         COUNT(*) as num_of_questions 
    FROM CHAPTERS AS c
    JOIN LESSONS AS l ON l.chapter_id = c.id
    JOIN QUESTIONS AS q ON q.lesson_id = l.id
GROUP BY c.id

不需要子查询——只需放宽 GROUP BY 子句即可。

原始查询中的 ORDER BY 对您没有任何作用，浪费资源 - 除非您使用 LIMIT。