主页 / dba / 问题 / 100463
Accepted
user3323654
Asked: 2015-05-04 05:06:07 +0800 CST

为什么这个 select 语句没有返回我提供的内容？

  • 772

我的 SQL Server 中有 3 个表我想知道为什么我的 select 语句不起作用或在 where 子句中给出我的条件的结果。我在那里学会了使用这种方法..

Subject table
SubjectID      CourseCode
   6            SubjectA
   7            SubjectB
   8            SubjectC

Schedule table
ScheduleID     Room
   15          Room1
   16          Room2
   17          Room3

AssignSchedule table
AssignScheduleID       ScheduleID       SubjectID
    14                     15               6
    15                     16               7

我想得到主题 SubjectA 的房间。为了得到这个我想出了这个选择语句查询

Select Subject.CourseCode, 
Schedule.Room from Subject, 
Schedule, AssignSchedule 
where 
AssignSchedule.ScheduleID = (Select Schedule.Schedule.ID 
from Schedule where Room = 'Room1') 
AND AssignSchedule.SubjectID = (Select Subject.SubjectID from Subject where CourseCode = 'SubjectA');

发生的事情是这样的

CourseCode      Room

  SubjectA      Room1
  SubjectB      Room1
  SubjectC      Room1
  SubjectA      Room2
  SubjectB      Room2
  SubjectC      Room2
  SubjectA      Room3
  SubjectB      Room3
  SubjectC      Room3

我的预期输出是这样的

CourseCode     Room
SubjectA       Room1

怎么了？帮助各位..

sql-server

1 个回答

  1. Best Answer

    WHERE 子句返回所需的单行 AssignSchedule，但您在该行与外部 Subject 和 Schedule 表之间没有关联，因此您获得了所有 Subject 和 Schedule 组合的笛卡尔积。您可以将外部表相关性添加到现有查询中以获得所需的结果：

    SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
      , Schedule
      , AssignSchedule
WHERE   AssignSchedule.ScheduleID = ( SELECT    Schedule.ScheduleID
                                      FROM      Schedule
                                      WHERE     Room = 'Room1'
                                    )
        AND AssignSchedule.SubjectID = ( SELECT Subject.SubjectID
                                         FROM   Subject
                                         WHERE  CourseCode = 'SubjectA'
                                       )
        AND AssignSchedule.ScheduleID = Schedule.ScheduleID
        AND AssignSchedule.SubjectID = Subject.SubjectID;

    更好的方法是在丑陋的子查询中使用简单的相等谓词。下面是使用您一直在使用的旧 ANSI SQL-89 语法以及等效的 ANSI SQL-92 语法的示例。我建议后者，因为它有助于避免丢失连接谓词。

    SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
      , Schedule
      , AssignSchedule
WHERE
            AssignSchedule.ScheduleID = Schedule.ScheduleID
        AND AssignSchedule.SubjectID = Subject.SubjectID
        AND Schedule.Room = 'Room1'
        AND Subject.CourseCode = 'SubjectA';

SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
JOIN AssignSchedule ON AssignSchedule.SubjectID = Subject.SubjectID
JOIN Schedule ON AssignSchedule.ScheduleID = Schedule.ScheduleID
WHERE   
        Schedule.Room = 'Room1'
    AND Subject.CourseCode = 'SubjectA';
    • 4

相关问题