SQL如何为初学者编写复杂查询？

Question

这是我的表格

 Student (sname, sid, gpa, level, deptno) 
 Course (cno, cname, deptno, units) 
 Dept (dname, deptno) 
 Takes (sid, cno) 

编写一个SQL查询，返回选修了更多专业课程外课程的学生姓名（即snames），可以假设数据库中所有学生至少选修了一个专业内课程。

我并不是在寻找这个问题的答案，但仍然欢迎任何回答。但我更希望人们告诉我如何生成编写一个像这样复杂查询的步骤。

我的答案是

 Select S.sname
 From Student S, Course C, Dept D, Takes T
 Where T.cno=C.cno and D.deptno=C.deptno and S.sid = T.sid
 Having COUNT(S.deptno=C.deptno) > COUNT( S.deptno != C.deptno)

我不确定在HAVING之后这样使用计数是否正确。 谢谢。

- runcode

---

这是为哪个班级的？ - Corey Adler

听起来像是斯坦福数据库课程的练习题。 - slashingweapon

我已经发布了解决方案，所以它不是为任何课程而准备的...大家保持冷静。 - runcode

@runcode：你可以将答案查询作为答案给出，不要将其包含在问题中。 - ypercubeᵀᴹ

Answer 1

有人认为这是硬件问题，但这里是标准解决方案：

 SELECT S.sname from Student S
 WHERE (SELECT COUNT (*) 
 FROM Takes T, Course C
 WHERE S.sid = T.sid AND T.cno = C.cno AND C.deptno = S.deptno)
 < (SELECT COUNT(*)
 FROM Takes T2, Course C2
 WHERE S.sid = T2.sid AND T2.cno = C2.cno AND C2.deptno != S.deptno)

- runcode

---

这是正确的，尽管我会使用JOIN而不是这个20年前的语法。 - ypercubeᵀᴹ

我的回答包含在问题中，这样正确吗？ - runcode

Answer 2

我经常处理相对复杂的SQL查询，尽管我从未学过数据库/SQL课程。我发现生成查询的最佳方法是一次只处理一个部分。例如，首先处理获取学生列表的部分，然后处理他们在系内外所选课程的数量，并最终在查询中比较这两个值并返回所需的最终结果集。

简而言之：小步前进。

- Ayush

---

以上的 COUNT 使用方式正确吗？ - runcode

Answer 3

您的第一次尝试，已经被纠正：

 SELECT S.sname
 FROM Student S, Course C, Dept D, Takes T
 WHERE T.cno = C.cno AND D.deptno = C.deptno AND S.sid = T.sid
 GROUP BY S.sid
 HAVING COUNT(CASE WHEN S.deptno =  C.deptno THEN 1 ELSE NULL END) 
      < COUNT(CASE WHEN S.deptno <> C.deptno THEN 1 ELSE NULL END) ;

并转换为SQL-92语法：

 SELECT S.sname
 FROM Student S 
   JOIN Takes T   ON S.sid = T.sid
   JOIN Course C  ON T.cno = C.cno
   JOIN Dept D    ON D.deptno = C.deptno
 GROUP BY S.sid        
        , S.sname                  --- this is not needed in SQL-2003
                                   --- but still required by most DBMS     
 HAVING 
        COUNT(CASE WHEN S.deptno =  C.deptno THEN 1 END) 
      < COUNT(CASE WHEN S.deptno <> C.deptno THEN 1 END) ;

                                   --- the ELSE NULL is not needed either
                                   --- it's the implied default

- ypercubeᵀᴹ

---

其实我只是猜测COUNT可以这样使用，因为我从来没有看到过任何使用COUNT的类似例子，你知道有哪些网站有很多SQL查询示例吗？ - runcode

COUNT(x) 计算 x 不为空的次数，因此它会计算 True 和 False。然而，您想要仅计算 True。 - ypercubeᵀᴹ

一个带有SQL练习的网站：**http://www.sql-ex.ru/** - ypercubeᵀᴹ