代码之家 › 专栏 › 技术社区 › Shiro

有确切结果的SQL选择

mysql sql

Shiro · 技术社区 · 14 年前

问一个简单的问题,只想让每个人都有乐趣去解决它。我有2张桌子。 1。学生 2。课程

学生

+----+--------+
| id | name   |
+----+--------+
|  1 | User1  |
|  2 | User2  |
+----+--------+

课程

+----+------------+------------+
| id | student_id | course_name|
+----+------------+------------+
|  1 |          1 | English    |
|  2 |          1 | Chinese    |
|  3 |          2 | English    |
|  4 |          2 | Japanese   |
+----+------------+------------+

我想让所有学过英语和汉语的学生,而不是英语或汉语的学生都能拿到这个成绩。

预期结果:

    +----+------------+------------+
    | id | student_id | course_name|
    +----+------------+------------+
    |  1 |          1 | English    |
    |  2 |          1 | Chinese    |
    +----+------------+------------+

我们通常做的是

select * from student join course on (student.id = course.student_id) WHERE course_name = 'English' OR course_name = 'Chinese'

但在这个结果中,我可以得到用户2记录,这不是我期望的结果。我只希望记录显示用户只参加英语+中文课程。

4 回复 | 直到 14 年前

araqnid 14 年前

只需参加两次课程:一次是英语,一次是汉语。即:

select student.*
from student
     join course english_course on student.id = english_course.student_id
     join course chinese_course on student.id = chinese_course.student_id
where english_course.course_name = 'English'
      and chinese_course.course_name = 'Chinese'

甚至

select * from student
where exists (select 1 from course
              where course.course_name = 'English' and course.student_id = student.id)
      and exists (select 1 from course
                  where course.course_name = 'Chinese' and course.student_id = student.id);

这也将从课程中删除重复的(学生ID、课程名称)条目。

我假设(学生ID,课程名称)被索引来驱动这两个。您的命名有点奇怪:“课程”表没有描述课程,它描述了从学生到课程的关联。就我个人而言,我称之为“学生课程”(或类似的课程,可能是后缀“_map”或“_link”),并让它包含一个“课程ID”,引用一个带有ID和名称的课程表。

(我也更喜欢使用一致命名的主键,而不是在自己的表中称它们为“id”,但这只是一种挑剔,而且更主观)

只是为了好玩:

select student.*
from student
     join course on student.id = course.student_id
where course.course_name = 'English'
intersect
select student.*
from student
     join course on student.id = course.student_id
where course.course_name = 'Chinese'

实际情况是,使用“intersect”比较基于相同表的两个结果集有点愚蠢。

Andomar 14 年前

可以使用HAVING子句强制匹配:

select  s.student_id
from    student s
join    course c 
on      s.id = c.student_id
        and c.course_name in ('English', 'Chinese')
group by
        s.student_id
having  count(distinct c.course_name) = 2

要检索其他列,可以在此查询上联接:

select  *
from    student s
join    course c 
on      s.id = c.student_id
join    (
        <query from above here>
        ) filter on s.id = filter.student_id

Ed. 14 年前

你可以用in运算符

select * from student join course on (student.id = course.student_id)
WHERE course_name = 'English' and student.id IN
(select student.id from student join course on (student.id = course.student_id)
 WHERE course_name = 'Chinese')

ivans 14 年前

您可能希望进行自我加入:

SELECT left.student_id sid1, right.student_id sid2,
       left.course_name cn1, right.course_name cn2
FROM   course left, course right
WHERE  sid1 = sid2 AND
       cn1 = 'English' AND cn2 = 'Chinese'

把它放到学生桌上不难。