一对多表联接?

我认为你的数据模型有缺陷。目前,您的模型每个房间有多个记录,每个插槽一个。因为您的查询只将学生限制在房间而不是插槽中,所以会产生交叉联接,这是错误的结果。

可以对查询进行组合以克服模型的缺点。不同的关键字是在这些场景中选择的钝器:

SQL> select *
  2      from ( select DISTINCT dorm_building, dorm_room from webRooms) wR
  3          LEFT JOIN residency R
  4          on wR.dorm_building = r.dorm_building
  5          and wr.dorm_room = r.dorm_room
  6  /

DORM_BUILDING         DORM_ROOM STUDENT_ID DORM_BUILDING         DORM_ROOM
-------------------- ---------- ---------- -------------------- ----------
my_dorm                       1        123 my_dorm                       1
my_dorm                       1        345 my_dorm                       1
my_dorm                       2

SQL>

一个更好的解决办法是用老虎机桌。这样就不需要有多个WebRoom记录来表示单个物理房间。你说学生被分配到哪个时段是“无关紧要的”,但学生被分配到一个特定时段是应用程序成功运行的关键。

以下是一些概念证明表:

create table webrooms
 (dorm_building varchar2(20)
    , dorm_room number)
/

create table slots
 (dorm_building varchar2(20)
    , dorm_room number
    , occupant_num number)
/

create table residency
 (student_id number
    , dorm_building varchar2(20)
    , dorm_room number
    , occupant_num number)
/

如您所见,修改后的查询清楚地显示了哪些插槽被占用,哪些插槽保持空闲状态:

SQL> select wr.*, s.occupant_num, r.student_id
  2      from webrooms wr
  3          INNER JOIN slots s
  4              on wr.dorm_building = s.dorm_building
  5              and wr.dorm_room = s.dorm_room
  6          LEFT JOIN residency r
  7              on s.dorm_building = r.dorm_building
  8              and s.dorm_room = r.dorm_room
  9              and s.occupant_num = r.occupant_num
 10  order by 1, 2, 3, 4
 11  /

DORM_BUILDING         DORM_ROOM OCCUPANT_NUM STUDENT_ID
-------------------- ---------- ------------ ----------
my_dorm                       1            1        123
my_dorm                       1            2        345
my_dorm                       2            1        678
my_dorm                       2            2
my_dorm                       2            3        890
my_dorm                       3            1
my_dorm                       3            2
my_dorm                       3            3
my_dorm                       4            1
my_dorm                       4            2        666

9 rows selected.

SQL>

或者,如果我们有一个支持透视查询的数据库(我在这里使用的是Oracle11g):

SQL> select * from (
  2      select wr.dorm_building||' #'||wr.dorm_room as dorm_room
  3             , num_gen.num as slot_number
  4             , case
  5                  when r.student_id is not null then r.student_id
  6                  when s.occupant_num is not null then 0
  7                  else null
  8               end as occupancy
  9          from webrooms wr
 10              CROSS JOIN ( select rownum as num from dual connect by level <= 4) num_gen
 11              LEFT JOIN slots s
 12                  on wr.dorm_building = s.dorm_building
 13                  and wr.dorm_room = s.dorm_room
 14                  and num_gen.num = s.occupant_num
 15              LEFT JOIN residency r
 16                  on s.dorm_building = r.dorm_building
 17                  and s.dorm_room = r.dorm_room
 18                  and s.occupant_num = r.occupant_num
 19      )
 20  pivot
 21      ( sum (occupancy)
 22        for slot_number in ( 1, 2, 3, 4)
 23      )
 24  order by dorm_room
 25  /

DORM_ROOM           1          2          3          4
---------- ---------- ---------- ---------- ----------
my_dorm #1        123        345
my_dorm #2        678          0        890
my_dorm #3          0          0          0
my_dorm #4          0        666

SQL>

您在APC的评论中提到,您所需要的只是可用性计数。如果事实上是这样,那么我认为下面的设计更有效:

Create Table Rooms  (
                        dorm_building ... Not Null
                        , dorm_room ... Not Null
                        , capacity int Not Null default ( 0 )
                        , Constraint PK_Rooms Primary Key ( dorm_building, dorm_room )
                        , ...
                        )

Create Table Residency  (
                            student_id ... Not Null Primary Key
                            , dorm_building ... Not Null
                            , dorm_room ... Not Null
                            , Constraint FK_Residency_Rooms
                                Foreign Key ( dorm_building, dorm_room )
                                References Rooms ( dorm_building, dorm_room )
                            , ...
                            )

我做的 student_id 中的主键 Residency 表只是因为没有提到时间元素,学生不可能同时在两个房间。现在,要获得可用空间,我们可以执行以下操作:

Select Rooms.dorm_building, Rooms.dorm_room
    , Rooms.Capacity
    , Coalesce(RoomCounts.OccupantTotal,0) As TotalOccupants
    , Rooms.Capacity - Coalesce(RoomCounts.OccupantTotal,0) As AvailableSpace
From Rooms
    Left Join   (
                Select R1.dorm_building, R1.dorm_room, Count(*) As OccupantTotal
                From Residency As R1
                Group By R1.dorm_building, R1.dorm_room
                ) As RoomCounts
        On RoomCounts.dorm_building = Rooms.dorm_building
            And RoomCounts.dorm_room = Rooms.dorm_room

现在,如果您还想显示“slots”,那么您应该动态计算它(假设SQL Server 2005及更高版本):

With Numbers As
    (
    Select Row_Number() Over ( Order By C1.object_id ) As Value
    From sys.columns As C1
        Cross Join sys.columns As C2
    )
    , NumberedResidency As
    (
    Select dorm_building, dorm_room, student_id
        , Row_Number() Over ( Partition By dorm_building, dorm_room Order By student_id ) As OccupantNum
    From Residency
    )
Select Rooms.dorm_building, Rooms.dorm_room, R.OccupantNum, R.StudentId
From Rooms
    Join Numbers As N
        On N.Value <= Rooms.Capacity
    Left Join NumberedResidency As R
        On R.dorm_building = Rooms.dorm_building
            And R.dorm_room = Rooms.dorm_room
            And N.Value = R.OccupantNum