我有两个我试图左连接的表,但我没有得到预期的结果。
房间在不同的日子有多个 child ,但是 child 只在他们开始后才算在一个房间里,如果他们在那天分配了时间。我试图实现的输出是这样的。
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');
我遇到问题的 SQL 是这样的。这可能不是正确的方法。
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;
在 LEFT JOIN 中的 Rows 上发生了翻倍的情况,这使计数失败了,我不知道如何防止它们。如果将 SELECT 替换为 * 可以看到效果
任何建议都会有很大帮助。
最佳答案
这类问题通常是由于在查询中过于宽泛的点进行聚合而出现的,这会导致记录重复计数。尝试在单独的子查询中聚合 Children
表:
SELECT
R.RoomName,
R.MaxNum,
R.MaxNum - C.Mon1 AS Mon1,
R.MaxNum - C.Tue1 AS Tue1,
R.MaxNum - C.Mon2 AS Mon2,
R.MaxNum - C.Tue2 AS Tue2
FROM Rooms R
LEFT JOIN
(
SELECT
RoomID,
COUNT(CASE WHEN MonHrs > 0 AND StartDate <= '2018-12-02'
THEN 1 END) AS Mon1,
COUNT(CASE WHEN TueHrs > 0 AND StartDate <= '2018-12-02'
THEN 1 END) AS Tue1,
COUNT(CASE WHEN MonHrs > 0 AND StartDate <= '2018-12-09'
THEN 1 END) AS Mon2,
COUNT(CASE WHEN TueHrs > 0 AND StartDate <= '2018-12-09'
THEN 1 END) AS Tue2
FROM Children
GROUP BY RoomID
) C
ON R.id = C.RoomID;
请注意,我们可以通过在开始日期使用条件聚合来避免原始查询中的双左连接。
后期编辑:您可能根本不需要子查询,q.v。 @Salman 的答案。但是我们的任何一个答案都应该解决重复计算的问题。
关于mysql - 同一个表上的 LEFT JOIN 加倍行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/53845799/