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

问题描述

我手头有一项任务，要求我返回一名学生的详细信息，该学生参加了一位姓 Hoffman 的老师教授的课程，但我被困住了.

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

这让我回到了霍夫曼教授的课程，但从这里我不知道该去哪里.我相信我必须访问已注册"表，然后最后是学生表，但尝试无济于事.以下查询是我在中断查询之前得到的 -_- 我确定我必须使用 HAVING 或 IN 关键字，但我没有很清楚如何处理它们！

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

任何帮助将不胜感激！

数据库结构如下:-

<块引用>

Student(sid integer, sname varchar(20), program varchar(4), level integer, age integer)Class(ccode varchar(6), cname varchar(25), week_day varchar(3), meet_at time, roomvarchar(6), tid 整数)已注册(sid 整数，ccode varchar(6))Tutor(tid 整数，tname varchar(20))

再次感谢:)

更新:-

SELECT DISTINCT *来自公众.学生"的INNER JOIN Public."Enrolled" e ON e.sid = s.sidINNER JOIN Public."Class" c ON c.ccode = e.ccodeINNER JOIN Public."Tutor" t ON t.tid = c.tidWHERE program='IT' AND t.tname LIKE '%Hoffman';

解决方案

以上两种解决方案，如果学生被同一老师的多个班级录取，会导致多次被举报.如果查询的唯一目标是只选择一次学生，那么下面的查询将完全做到这一点.

选择 *FROM 学生WHERE s.programme = 'IT'并且存在 (选择 *FROM 已注册在 c.ccode = e.ccode 上加入 c 类加入导师 t ON t.tid = c.tid其中 e.sid = s.sidAND t.tname LIKE '%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');

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')
    );

Any help would be much appreciated!

The database structures are as follows:-

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))

Thanks again :)

Update:-

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屋！