我有两个表，我试图左连接，但我没有得到预期的结果。
房间在不同的日子有多个孩子，但是孩子们只有在他们开始后才被计算在一个房间里，如果他们在那一天有时间分配的话。我想要达到的效果是这样的。

Room  | MaxNum | Mon(Week1) | Tue(Week1) | Mon(Week2) | Tue(Week2)
Blue  | 5      | 4          | 4          | 3          | 2
Green | 10     | 10         | 10         | 9          | 9  
Red   | 15     | 15         | 15         | 15         | 15

这是模式和一些数据。。。

create table Rooms(
  id       INT,
  RoomName VARCHAR(10),
  MaxNum   INT
);

create table Children (
  id          INT,
  RoomID      INT,
  MonHrs      INT,
  TueHrs      INT,
  StartDate   DATE
);

INSERT INTO Rooms VALUES (1, 'Blue', 5);
INSERT INTO Rooms VALUES (2, 'Green', 10);
INSERT INTO Rooms VALUES (3, 'Red', 15);

INSERT INTO Children VALUES (1, 1, 5, 0, '2018-12-02');
INSERT INTO Children VALUES (2, 1, 0, 5, '2018-12-02');
INSERT INTO Children VALUES (3, 1, 5, 5, '2018-12-09');
INSERT INTO Children VALUES (4, 1, 0, 5, '2018-12-09');
INSERT INTO Children VALUES (5, 2, 5, 0, '2018-12-09');
INSERT INTO Children VALUES (6, 2, 0, 5, '2018-12-09');

我遇到的问题是这个。这可能不是正确的方法。

SELECT R.RoomName, R.MaxNum,
       R.MaxNum - SUM(CASE WHEN C1.MonHrs > 0 THEN 1 ELSE 0 END) AS Mon1,
       R.MaxNum - SUM(CASE WHEN C1.TueHrs > 0 THEN 1 ELSE 0 END) AS Tue1,
       R.MaxNum - SUM(CASE WHEN C2.MonHrs > 0 THEN 1 ELSE 0 END) AS Mon2,
       R.MaxNum - SUM(CASE WHEN C2.TueHrs > 0 THEN 1 ELSE 0 END) AS Tue2
  FROM Rooms R
        LEFT JOIN Children C1 
          ON R.id = C1.RoomID
         AND C1.StartDate <= '2018-12-02'     
        LEFT JOIN Children C2
          ON R.id = C2.RoomID
         AND C2.StartDate <= '2018-12-09'
 GROUP BY R.RoomName;

在左连接中的行上发生了一个双向上的情况，这使计数偏离了方向，我不知道如何防止它们。如果将select替换为*

任何建议都会大有帮助。