mysql - 如何通过基于少数列值删除重复项来从两个表中获取数据

Question

我有两个表，它们的列数相同，但有几列数据不同。

示例:第一个表数据

 Apt_ID  Res_ID    Subject       SartTime              EndTime           Color 
   0       1        Avail    06-10-2015 08:00:0    06-10-2015 08:30:0     Blue 
   0       1        Avail    06-10-2015 08:30:0    06-10-2015 09:00:0     Blue
   0       2        Avail    06-15-2015 08:00:0    06-15-2015 08:30:0     Black  

第二个表的结果

 Apt_ID  Res_ID    Subject       SartTime              EndTime           Color 
   1       1       Booked    06-10-2015 08:00:0    06-10-2015 08:30:0     Blue 
   2       1       Booked    06-10-2015 08:30:0    06-10-2015 08:40:0     Blue

我需要的是:

1. 我需要合并两个表中的记录，然后替换第一个表中的记录与第二个表中的记录(如果值位于)Res_ID 、 startTime 和 endTime 列相匹配。
2. 如果 table_2 中的记录位于 table_1 的开始时间段和结束时间段之间，

我需要替换table_1中的记录并为剩余时间段添加新记录(请参阅表1和表2中的第二行)。

预期结果:

  Apt_ID  Res_ID    Subject       SartTime              EndTime           Color 
   1       1        Booked    06-10-2015 08:00:0    06-10-2015 08:30:0     Blue 
   2       1        Booked    06-10-2015 08:30:0    06-10-2015 08:40:0     Blue
   0       1        Avail     06-10-2015 08:40:0    06-10-2015 09:00:0     Blue
   0       2        Avail     06-15-2015 08:00:0    06-15-2015 08:30:0     Black

希望我说得有道理，如果没有请告诉我。

我是 SQL 新手，我正在尽力完成这项工作。到目前为止，我已经尝试使用“UNION”，但由于列值不相同，记录不会被替换并且会重复。我也正在通过使用游标来解决这个问题，但想知道是否有有效的解决方案。

Answer 1

编辑:MySQL 不支持完整的连接语法。谢谢@jarlh——当我认为是基本 SQL 的东西不在 MYSQL 中时，我总是感到惊讶。然而，可以使用 RIGHT 和 LEFT 连接的 UNION 来充分模拟它。我将在下面展示这一点。

我不得不读了几次这个问题，但我想我至少理解了你所问的部分内容。

你绝对不需要游标。您可能可以使用 UNION，但我会使用 FULL JOIN 和 COALESCE 来生成结果集。

--创建测试数据

CREATE TABLE Avail (Apt_ID INT,  Res_ID INT,  Subject VARCHAR(10), StartTime DATETIME, EndTime DATETIME, Color VARCHAR(10))
    INSERT INTO Avail (Apt_ID ,Res_ID,Subject,StartTime,EndTime,Color) VALUES (0,1,'Avail','06-10-2015 08:00:00','06-10-2015 08:30:0','Blue')
    INSERT INTO Avail (Apt_ID ,Res_ID,Subject,StartTime,EndTime,Color) VALUES (0,1,'Avail','06-10-2015 08:30:0','06-10-2015 09:00:0','Blue')
    INSERT INTO Avail (Apt_ID ,Res_ID,Subject,StartTime,EndTime,Color) VALUES (0,2,'Avail','06-15-2015 08:00:0','06-15-2015 08:30:0','Black')

    CREATE TABLE Booked (Apt_ID INT,  Res_ID INT,  Subject VARCHAR(10), StartTime DATETIME, EndTime DATETIME, Color VARCHAR(10))

    INSERT INTO Booked (Apt_ID ,Res_ID,Subject,StartTime,EndTime,Color) VALUES (1,1,'Booked','06-10-2015 08:00:0','06-10-2015 08:30:0','Blue')
    INSERT INTO Booked (Apt_ID ,Res_ID,Subject,StartTime,EndTime,Color) VALUES (2,1,'Booked','06-10-2015 08:30:0','06-10-2015 08:40:0','Blue')

--get the combined result set, preferrin values from the booked table.
    SELECT COALESCE(b.Apt_ID,a.Apt_ID) Apt_ID
          ,COALESCE(b.Subject,a.Subject) Subject
          ,COALESCE(a.Res_ID,b.Res_ID) Res_ID
          ,COALESCE(a.StartTime,b.StartTime) StartTime
          ,COALESCE(a.EndTime,b.EndTime) EndTime
          ,COALESCE(b.Color,a.Color) Color
      FROM Avail a
           FULL JOIN 
           Booked b ON a.Res_ID=b.Res_ID
                   AND a.StartTime=b.StartTime
                   AND a.EndTime=b.EndTime

编辑:使用 MySQL 的完全连接模拟:

SELECT COALESCE(b.Apt_ID,a.Apt_ID) Apt_ID
      ,COALESCE(b.Subject,a.Subject) Subject
      ,a.Res_ID
      ,a.StartTime
      ,a.EndTime
      ,COALESCE(b.Color,a.Color) Color
  FROM Avail a LEFT JOIN Booked b ON a.Res_ID=b.Res_ID
                            AND a.StartTime=b.StartTime
                            AND a.EndTime=b.EndTime
UNION
SELECT COALESCE(b.Apt_ID,a.Apt_ID) Apt_ID
      ,COALESCE(b.Subject,a.Subject) Subject
      ,b.Res_ID
      ,b.StartTime
      ,b.EndTime
      ,COALESCE(b.Color,a.Color) Color
 FROM Avail a RIGHT JOIN Booked b ON a.Res_ID=b.Res_ID
                                 AND a.StartTime=b.StartTime
                                 AND a.EndTime=b.EndTime

编辑 2:MySQL 比 TSQL 更难。无需使用 FULL JOIN、ISNULL、ROW_NUMBER 或 CTE。几乎可以肯定有一种更好的方法可以做到这一点，但它似乎有效。

Fiddle显示这一点。您需要一个临时表，但 SQLFiddle.com 正在提示。

CREATE TABLE Avail (Apt_ID INT,  Res_ID INT,  Subject VARCHAR(10), StartTime DATETIME, EndTime DATETIME, Color VARCHAR(10));
INSERT INTO Avail (Apt_ID ,Res_ID,Subject,StartTime,EndTime,Color) VALUES (0,1,'Avail','2015-06-10 08:00:00','2015-06-10 08:30:0','Blue');
INSERT INTO Avail (Apt_ID ,Res_ID,Subject,StartTime,EndTime,Color) VALUES (0,1,'Avail','2015-06-10 08:30:0','2015-06-10 09:00:0','Blue');
INSERT INTO Avail (Apt_ID ,Res_ID,Subject,StartTime,EndTime,Color) VALUES (0,2,'Avail','2015-06-15 08:00:0','2015-06-15 08:30:0','Black');

CREATE TABLE Booked (Apt_ID INT,  Res_ID INT,  Subject VARCHAR(10), StartTime DATETIME, EndTime DATETIME, Color VARCHAR(10));
INSERT INTO Booked (Apt_ID ,Res_ID,Subject,StartTime,EndTime,Color) VALUES (1,1,'Booked','2015-06-10 08:00:0','2015-06-10 08:30:0','Blue');
INSERT INTO Booked (Apt_ID ,Res_ID,Subject,StartTime,EndTime,Color) VALUES (2,1,'Booked','2015-06-10 08:30:0','2015-06-10 08:40:0','Blue');

CREATE TEMPORARY TABLE tmp (Apt_ID INT, Subject VARCHAR(10), Res_ID INT, StartTime DateTime, EndTime DateTime, Color VARCHAR(10), rn INT);

SET @row_number=0;

INSERT INTO tmp (Apt_ID , Subject , Res_ID , StartTime , EndTime , Color, rn)
SELECT Apt_ID , Subject , Res_ID , StartTime , EndTime , Color, 
       (@row_number:=@row_number+1) rn
  FROM (
          SELECT COALESCE(b.Apt_ID,a.Apt_ID) Apt_ID
                  ,COALESCE(b.Subject,a.Subject) Subject
                  ,a.Res_ID
                  ,a.StartTime
                  ,a.EndTime
                  ,COALESCE(b.Color,a.Color) Color
              FROM Avail a LEFT JOIN Booked b ON a.Res_ID=b.Res_ID
                                        AND a.StartTime=b.StartTime
                                        AND a.EndTime=b.EndTime
            UNION
            SELECT COALESCE(b.Apt_ID,a.Apt_ID) Apt_ID
                  ,COALESCE(b.Subject,a.Subject) Subject
                  ,b.Res_ID
                  ,b.StartTime
                  ,b.EndTime
                  ,COALESCE(b.Color,a.Color) Color
             FROM Avail a RIGHT JOIN Booked b ON a.Res_ID=b.Res_ID
                                             AND a.StartTime=b.StartTime
                                             AND a.EndTime=b.EndTime
        ) x
  ORDER BY Res_ID,StartTime,EndTime

SELECT Apt_ID, Subject, Res_ID
      ,CASE WHEN COALESCE((SELECT EndTime FROM tmp b WHERE b.Res_ID = a.Res_ID AND b.rn = a.rn - 1),0) > StartTime 
            THEN (SELECT EndTime FROM tmp b WHERE  b.Res_ID = a.Res_ID AND b.rn = a.rn - 1)
            ELSE StartTime END StartTime
      ,EndTime
      ,Color
 FROM tmp a