PostgreSQL多个子查询 [英] postgresql multiple subqueries

问题描述

我手头上有一项任务，要求我返回一名学生的详细信息，该学生参加了由霍夫曼(Hoffman)姓氏的老师教的课程，而我被困住了.

I have a task at hand which requires me to return the details of a student who is enrolled in a class taught by a teacher with the surname of Hoffman and I'm stuck.

    SELECT * FROM Public."Class" WHERE tid=(
        SELECT tid FROM Public."Tutor" WHERE tname LIKE '%Hoffman');

这给我返回了霍夫曼教授的课程，但是从这里我不确定该去哪里.我认为我必须先访问已注册"表，然后再访问学生"表，但尝试无济于事.以下查询是我在中断查询之前所获得的最新信息-_-我敢肯定我必须使用HAVING或IN关键字，但我不太了解如何处理它们！

This returns to me the classes taught by Hoffman but from here I'm not sure where to go. I believe I have to access the 'Enrolled' table and then finally the student table but have tried to no avail. The following query is as far as I got before breaking the query -_- I'm sure i'll have to use the HAVING or IN keyword but I don't quite know what to do with them!

SELECT * FROM Public."Student" WHERE programme='IT' (
    SELECT * FROM Public."Class" WHERE tid=(
        SELECT tid FROM Public."Tutor" WHERE tname LIKE '%Hoffman')
    );

任何帮助将不胜感激！

数据库结构如下:-

Student(sid integer, sname varchar(20), programme varchar(4), level integer, age integer) 
Class(ccode varchar(6), cname varchar(25), week_day varchar(3), meets_at time, room 
varchar(6), tid integer) 
Enrolled(sid integer, ccode varchar(6)) 
Tutor(tid integer, tname varchar(20))

再次感谢:)

更新:-

SELECT DISTINCT *
FROM Public."Student" s
INNER JOIN Public."Enrolled" e ON e.sid = s.sid
INNER JOIN Public."Class" c ON c.ccode = e.ccode
INNER JOIN Public."Tutor" t ON t.tid = c.tid
WHERE programme='IT' AND t.tname LIKE '%Hoffman';

推荐答案

以上两种解决方案将导致如果同一位学生参加多个课程的学生被多次举报.如果查询的唯一目标是只选择一次学生，则下面的查询将完全做到这一点.

The two solutions above will result in students to be reported multiple times if they are enrolled in multiple classes from the same teacher. If the only goal of the query is to select students only once, the query below will do exactly that.

SELECT *
FROM Student s
WHERE s.programme = 'IT'
AND EXISTS (
  SELECT * 
  FROM Enrolled e
  JOIN Class c ON c.ccode = e.ccode
  JOIN Tutor t ON t.tid = c.tid
  WHERE e.sid = s.sid
  AND t.tname LIKE '%Hoffman'
  );

这篇关于PostgreSQL多个子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！