如何创建此sql语句以在多个表端搜索每个记录？

Question

现在，我必须为搜索页面创建一个查询，它依赖于有30个表的数据库：

我将以一个例子来描述我的要求：

表A和表B ->之间存在一对多的关系

如果我们认为表A是学生信息，表B每年为学生保存课程。每年只允许选修3门学科，其结构如下：

表A: ID，学生姓名，Tel#，生日.

表B: :ID，StudentID，Date，Subject-1，Subject-2，Subject-3

因此，学生每年可以选修任意3门课程，我想要查询一个名为“X”的主题，让所有在任何一年都能学到它的学生，但只有一次。

有效学生示例：

  StudentID  Sub1  Sub2  Sub3  Year  
  60         Z     (X)    Y      1
  60         L      W     V      2
  60         M      P     Y      3

无效学生示例：

  StudentID  Sub1  Sub2  Sub3  Year  
  10         Z     (X)    O      1
  10         L      W     V      2
  10         O      P    (x)     3

我希望这一点对我的问题足够清楚

Answer 1

我同意这样的意见:在表2中，每一主题一行都会更好(标准化的结构实际上总是更好，更适合SQL )。

也就是说，你可以在你的结构中解决它。

SELECT
  studentID
FROM
  table2
WHERE
     sub1 = 'x'
  OR sub2 = 'x'
  OR sub3 = 'x'
GROUP BY
  studentID
HAVING
  COUNT(*) = 1

(这假设学生不能在同一年内选修同一科目两次。)

某些类型的SQL可能允许缩短WHERE子句.

WHERE
  'x' IN (sub1, sub2, sub3)

然后你可以把它放在一个子查询中来获取所有的学生细节.

SELECT
  *
FROM
  table1
INNER JOIN
(
  <put the above query here>
)
  AS lookup
    ON lookup.studentID = table1.studentID

编辑

我不明白你的评论。但另一种(而且可能更通用)的从句可能是..。

HAVING
  SUM(CASE WHEN sub1 = 'x' THEN 1 ELSE 0 END +
      CASE WHEN sub2 = 'x' THEN 1 ELSE 0 END +
      CASE WHEN sub3 = 'x' THEN 1 ELSE 0 END)
  = 1

Answer 2

这样做的一种方法..。

对于给定的主题X，您可以遍历一个循环，让每个学生检查他是否是这个科目的有效学生。在循环内部：

IF(
(SELECT count(1) from TableB where Sub1 = 'X' and StudentID = 60 ) + 
(SELECT count(1) from TableB where Sub2 = 'X' and StudentID = 60 ) + 
(SELECT count(1) from TableB where Sub3 = 'X'and StudentID = 60 ) ) = 1
BEGIN
    INSERT INTO #ValidStudent(...)
END  

您将不得不将StudentID变量替换为60，并在最后。

SELECT * FROM #ValidStudent