首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >在相同的表上左联接加倍行

问在相同的表上左联接加倍行
EN

Stack Overflow用户
提问于 2018-12-19 06:36:50
回答 2查看 777关注 0票数 4

我有两个表,我试图离开加入,但我没有得到预期的结果。

房间在不同的日子里有多个孩子,但是孩子们只有在他们开始工作后,如果他们在那天有时间的话,才会被计算在一个房间里。我想要达到的结果是这样的。

代码语言:javascript
复制
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 

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

代码语言:javascript
复制
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。这可能不是正确的方法。

代码语言:javascript
复制
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,则可以看到效果。

​

​

任何建议都会有很大帮助。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-12-19 06:42:16

这类问题通常出现在查询中过于宽泛的点上进行聚合,然后导致记录的重复计数。尝试在单独的子查询中聚合Children表:

代码语言:javascript
复制
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。答案是:萨勒曼。但我们的任何一个答案都应该解决重复计算的问题。

票数 2
EN

Stack Overflow用户

发布于 2018-12-19 07:05:02

您需要使用一个左联接,并将日期筛选器从联接条件移到聚合中:

代码语言:javascript
复制
SELECT R.id, R.RoomName, R.MaxNum
    , R.MaxNum - COUNT(CASE WHEN C.StartDate <= '2018-12-02' AND C.MonHrs > 0 THEN 1 END) AS Mon1
    , R.MaxNum - COUNT(CASE WHEN C.StartDate <= '2018-12-02' AND C.TueHrs > 0 THEN 1 END) AS Tue1
    , R.MaxNum - COUNT(CASE WHEN C.StartDate <= '2018-12-09' AND C.MonHrs > 0 THEN 1 END) AS Mon2
    , R.MaxNum - COUNT(CASE WHEN C.StartDate <= '2018-12-09' AND C.TueHrs > 0 THEN 1 END) AS Tue2
FROM Rooms R
LEFT JOIN Children C ON R.id = C.RoomID
GROUP BY R.id, R.RoomName, R.MaxNum
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/53845799

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档