mySQL连接表重复问题

Question

我有 2 个表(pcgroup 和客户端 pc)。假设今天是 11 月 24 日，我需要仅使用 mySQL 查找最近 2 天哪台 PC 在线以及哪台 PC 不在线。

INSERT INTO pcgroup(id, groupName) 
  VALUES(1, 'defaultGroup');
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) 
  VALUES(1, 1, 'pc1', '2011-11-24');
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) 
  VALUES(2, 1, 'pc2', '2011-11-24');
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) 
  VALUES(3, 1, 'pc3', '2011-11-20');
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) 
  VALUES(4, 1, 'pc4', '2011-11-20');
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) 
  VALUES(5, 1, 'pc5', '2011-11-20');

这是我现在的查询

SELECT DISTINCT
  pcgroup.id AS pcGroupID, pcgroup.groupName,
  online.onlinePC, offline.offlinePC
FROM
  (
    SELECT   
      pcgroup.Id, pcGroup.groupName
    FROM
      pcgroup
    WHERE
      (pcgroup.Id = 1)
  ) pcgroup
LEFT  JOIN
  (
    SELECT
      clientpc.Id, clientpc.pcGroupId, clientpc.clientPcName AS onlinePC
    FROM
      clientpc
    WHERE
      DateDiff(CURDATE(),clientpc.lastOnlineTime) <= 2
    AND
      DateDiff(CURDATE(),clientpc.lastOnlineTime) IS NOT NULL
  ) online  
 ON 
  pcgroup.Id = online.pcGroupId
 LEFT JOIN
   (
     SELECT
       clientpc.Id, clientpc.pcGroupId, clientpc.clientPcName AS offlinePC
     FROM
       clientpc
     WHERE
       (DateDiff(CURDATE(),clientpc.lastOnlineTime) > 2
     OR
        DateDiff(CURDATE(),clientpc.lastOnlineTime) IS NULL)
   ) offline 
  ON pcgroup.Id = offline.pcGroupId

这是我得到的结果

*pcGroupID    groupName        onlinePC       offlinePC*
  1           defaultGroup      pc1             pc3
  1           defaultGroup      pc1             pc4
  1           defaultGroup      pc1             pc5
  1           defaultGroup      pc2             pc3
  1           defaultGroup      pc2             pc4
  1           defaultGroup      pc2             pc5

但是，我需要的是这样的东西

*pcGroupID    groupName        onlinePC       offlinePC*
  1           defaultGroup      pc1             pc3
  1           defaultGroup      pc2             pc4
  1           defaultGroup                      pc5

所以我的问题是，这可以实现吗？如果是，如何。已经处理此查询 2 天了。所以，如果你们能帮助我，我真的很感激。

Answer 1

我启动了一个 MySQL 实例并模拟了 Postgres 解决方案中使用的 rowid。创建脚本:

CREATE TABLE pcgroup(id int, groupName varchar(64));
CREATE TABLE clientpc(id int, pcGroupId int, clientPcName varchar(64), lastOnlineTime date);

INSERT INTO pcgroup(id, groupName) VALUES(1, 'defaultGroup');
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(1, 1, 'pc1', CURRENT_DATE);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(2, 1, 'pc2', CURRENT_DATE);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(3, 1, 'pc3', CURRENT_DATE-4);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(4, 1, 'pc4', CURRENT_DATE-4);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(5, 1, 'pc5', CURRENT_DATE-4);

INSERT INTO pcgroup(id, groupName) VALUES(2, 'group2');
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(6, 2, 'pc6', CURRENT_DATE-4);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(7, 2, 'pc7', CURRENT_DATE-4);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(8, 2, 'pc8', CURRENT_DATE);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(9, 2, 'pc9', CURRENT_DATE);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(10, 2, 'pc10', CURRENT_DATE);

脚本(遵循与 Postgres 解决方案相同的逻辑):

-- Apply sort to union
SELECT pcGroupID, groupName, onlinePC, offlinePC
  FROM (
        SELECT online.pcGroupID, online.groupName, online.clientPcName AS onlinePC, IFNULL(offline.clientPcName, '-') AS offlinePC

          FROM (-- Apply a groupName-based row number to the list of "online" PCs
                SELECT pcGroupID, groupName, clientPcName, lastOnlineTime
                      ,if(@lastGroupID!=pcGroupID
                          ,CONCAT_WS('_', pcGroupID, @curRow := 1)
                          ,CONCAT_WS('_', pcGroupID, @curRow := @curRow + 1)) AS row_number
                          ,@lastGroupID := pcGroupID
                  FROM (-- Filter to the list of online PCs
                        SELECT g.id AS pcGroupID, g.groupName, c.clientPcName, c.lastOnlineTime
                          FROM pcgroup g
                              ,clientpc c
                          WHERE c.pcGroupId = g.id
                            AND c.lastOnlineTime >= CURRENT_DATE - 2
                          ORDER BY g.id, c.clientPcName ) x
                      ,(SELECT @curRow := 0) r ) AS online

               LEFT OUTER JOIN (

                -- Apply a groupName-based row number to the list of "offline" PCs
                SELECT pcGroupID, groupName, clientPcName, lastOnlineTime
                      ,if(@lastGroupID!=pcGroupID
                          ,CONCAT_WS('_', pcGroupID, @curRow := 1)
                          ,CONCAT_WS('_', pcGroupID, @curRow := @curRow + 1)) AS row_number
                          ,@lastGroupID := pcGroupID
                  FROM (-- Filter to the list of offline PCs
                        SELECT g.id AS pcGroupID, g.groupName, c.clientPcName, c.lastOnlineTime
                          FROM pcgroup g
                              ,clientpc c
                          WHERE c.pcGroupId = g.id
                            AND c.lastOnlineTime < CURRENT_DATE - 2
                          ORDER BY g.id, c.clientPcName ) x
                      ,(SELECT @curRow := 0) r ) AS offline

            ON (online.row_number = offline.row_number)


UNION

        SELECT offline.pcGroupID, offline.groupName, IFNULL(online.clientPcName, '~') AS onlinePC, offline.clientPcName AS offlinePC

          FROM (-- Apply a groupName-based row number to the list of "online" PCs
                SELECT pcGroupID, groupName, clientPcName, lastOnlineTime
                      ,if(@lastGroupID!=pcGroupID
                          ,CONCAT_WS('_', pcGroupID, @curRow := 1)
                          ,CONCAT_WS('_', pcGroupID, @curRow := @curRow + 1)) AS row_number
                          ,@lastGroupID := pcGroupID
                  FROM (-- Filter to the list of online PCs
                        SELECT g.id AS pcGroupID, g.groupName, c.clientPcName, c.lastOnlineTime
                          FROM pcgroup g
                              ,clientpc c
                          WHERE c.pcGroupId = g.id
                            AND c.lastOnlineTime >= CURRENT_DATE - 2
                          ORDER BY g.id, c.clientPcName ) x
                      ,(SELECT @curRow := 0) r ) AS online

               RIGHT OUTER JOIN (

                -- Apply a groupName-based row number to the list of "offline" PCs
                SELECT pcGroupID, groupName, clientPcName, lastOnlineTime
                      ,if(@lastGroupID!=pcGroupID
                          ,CONCAT_WS('_', pcGroupID, @curRow := 1)
                          ,CONCAT_WS('_', pcGroupID, @curRow := @curRow + 1)) AS row_number
                          ,@lastGroupID := pcGroupID
                  FROM (-- Filter to the list of offline PCs
                        SELECT g.id AS pcGroupID, g.groupName, c.clientPcName, c.lastOnlineTime
                          FROM pcgroup g
                              ,clientpc c
                          WHERE c.pcGroupId = g.id
                            AND c.lastOnlineTime < CURRENT_DATE - 2
                          ORDER BY g.id, c.clientPcName ) x
                      ,(SELECT @curRow := 0) r ) AS offline

            ON (online.row_number = offline.row_number)

    ) z ORDER BY pcGroupID, groupName, OnlinePC, offlinePC

结果:

1   defaultGroup  pc1    pc3
1   defaultGroup  pc2    pc4
1   defaultGroup  ~      pc5
2   group2        pc10   pc6
2   group2        pc8    pc7
2   group2        pc9    -

-- PostgreSQL--

我在 Postgres 中试过了。这个查询看起来比实际情况更可怕。有许多功能可以缩短它:子查询分解(即使用 WITH)、伪行号生成器、完全外部连接)。我不确定 mysql 是否有那个，所以我没有使用那些函数。

我认为要点是您要的是两个并不真正相关的不同列表:onlinePCs 和 offlinePCs。您只想将两个列表并排放置。为此，您可以引入行计数伪列来创建两个列表之间的关系。第 1 步生成在线 PC 列表并计算每个组的数量(生成行标识符 _。然后根据该行标识符将其加入离线 PC 列表。如果离线 PC 多于在线 PC，离线 PC 不会出现在此列表中。这就是我们在第 4 步中再次执行整个操作的原因，但这次由离线 PC 驱动，以解决离线 PC 多于在线的情况PC。UNION 将去除重复项。

我还使用了 CURRENT_DATE 并将 2 硬编码为离线和在线之间的天数。您需要尝试一下。

创建脚本:

CREATE TABLE pcgroup(id bigint, groupName varchar);
CREATE TABLE clientpc(id bigint, pcGroupId bigint, clientPcName varchar, lastOnlineTime date);

INSERT INTO pcgroup(id, groupName) VALUES(1, 'defaultGroup');
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(1, 1, 'pc1', CURRENT_DATE);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(2, 1, 'pc2', CURRENT_DATE);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(3, 1, 'pc3', CURRENT_DATE-4);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(4, 1, 'pc4', CURRENT_DATE-4);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(5, 1, 'pc5', CURRENT_DATE-4);

INSERT INTO pcgroup(id, groupName) VALUES(2, 'group2');
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(6, 2, 'pc6', CURRENT_DATE-4);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(7, 2, 'pc7', CURRENT_DATE-4);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(8, 2, 'pc8', CURRENT_DATE-4);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(9, 2, 'pc9', CURRENT_DATE);
INSERT INTO clientpc(id, pcGroupId, clientPcName, lastOnlineTime) VALUES(10, 2, 'pc10', CURRENT_DATE);

查询:

SELECT online.pcGroupID, online.groupName, online.clientPcName AS onlinePC, offline.clientPcName AS offlinePC

    -- 1: Get the list of online PCs, and give them a group based pseudo rownumber
  FROM (SELECT g.id AS pcGroupID, g.groupName, c.clientPcName
              ,g.id || '_' || row_number() OVER(PARTITION BY g.id ORDER BY g.id, c.clientPcName) AS rownum
          FROM pcgroup g
              ,clientpc c
          WHERE c.pcGroupId = g.id
            AND lastOnlineTime > CURRENT_DATE - 2) AS online

    -- 2: Get the list of offline PCs, and give them a group based pseudo rownumber
       LEFT OUTER JOIN (SELECT g.id AS pcGroupID, g.groupName, c.clientPcName
                              ,g.id || '_' || row_number() OVER(PARTITION BY g.id ORDER BY g.id, c.clientPcName) AS rownum
                         FROM pcgroup g
                             ,clientpc c
                         WHERE c.pcGroupId = g.id
                           AND lastOnlineTime <= CURRENT_DATE - 2) AS offline

       -- 3: Join the list together: this will only include rows for the number of "online" pcs that exist
       ON (online.rownum = offline.rownum)

-- 4: Repeat 1-3, but this time base it on offline pcs and it will only include rows for the number of "offline" pcs that exist
--    The UNION will dump the duplicates

UNION

SELECT offline.pcGroupID, offline.groupName, online.clientPcName AS onlinePC, offline.clientPcName AS offlinePC
  FROM (SELECT g.id AS pcGroupID, g.groupName, c.clientPcName
              ,g.id || '_' || row_number() OVER(PARTITION BY g.id ORDER BY g.id, c.clientPcName) AS rownum
          FROM pcgroup g
              ,clientpc c
          WHERE c.pcGroupId = g.id
            AND lastOnlineTime > CURRENT_DATE - 2) AS online

       RIGHT OUTER JOIN (SELECT g.id AS pcGroupID, g.groupName, c.clientPcName
                              ,g.id || '_' || row_number() OVER(PARTITION BY g.id ORDER BY g.id, c.clientPcName) AS rownum
                         FROM pcgroup g
                             ,clientpc c
                         WHERE c.pcGroupId = g.id
                           AND lastOnlineTime <= CURRENT_DATE - 2) AS offline
       ON (online.rownum = offline.rownum)

结果:

 pcgroupid |  groupname   | onlinepc | offlinepc
-----------+--------------+----------+-----------
         1 | defaultGroup | pc1      | pc3
         1 | defaultGroup | pc2      | pc4
         1 | defaultGroup |          | pc5
         2 | group2       | pc10     | pc6
         2 | group2       | pc8      | pc7
         2 | group2       | pc9      |
(6 rows)