两个表中合并记录到第三个使用SQL

问题描述：

我有3个表A，B，C两个表中合并记录到第三个使用SQL

所有3代表的架构是相同的许多记录（约1000万）

现在A和B中的一些记录是相同的id和一些不同Id

现在我想合并来自A和B的记录到表C。合并逻辑如下

1)If records with id = someId is present only in A or B (only in one table) 
    then insert record from that table (A/B depending of where its present) in table C 
2)If records with id =someId is present in both A and B { 

      if(A.time1 > B.time2){ 
      insert record from A into C 
      }else{ 
      insert record from B into C 
      } 
    }

我想用一个SQL查询来做到这一点，利用外A和B之间的连接。但我不清楚如何去做。

你尝试过什么吗？ –

我认为你的意思是专栏不是模式 –

答

假设表C是空的，那么这样的事情会做的伎俩

INSERT INTO C   
SELECT A.* 
    FROM A LEFT OUTER JOIN B 
    ON A.ID = B.ID 
WHERE (A.TIME1 > B.TIME1 OR B.TIME1 IS NULL) 
UNION 
SELECT B.* 
    FROM B LEFT OUTER JOIN A 
    ON B.ID = A.ID 
WHERE (A.TIME1 <= B.TIME1 OR A.ID IS NULL)

嗯..对于那个谁投下了..它确定如果你不喜欢我的答案..但你可以告诉我你到底不喜欢什么？如果我的解决方案出现问题，请告诉我 –

：谢谢你的回答。但是这个解决方案只处理表A和表B都有匹配的情况。如果记录存在于A而不在B中（反之亦然），我们需要在C中插入这些数据。我们照顾那个？ – user93796

@ user93796它处理其他记录（存在于A中，但不存在于B中，反之亦然），这就是为什么它是“左外连接”和“联合”。左外连接将获得A中的所有记录，而不是B中的所有记录，并且时间比B时间更晚/更晚，与第二个查询相同。并且'union'会合并记录 –

答

组合3个结果集：

记录与共享的ID。
记录与IDS只存在于
记录与IDS仅存在在B

1.，2。可组合成1个左连接，3是右连接过滤以唯一的ID发生。选择a作为2.的数据源，b选择3，a/b取决于1的优先顺序。可以将选择逻辑压缩为单个条件，因为如果没有任何值为空，则比较将只产生布尔值，即。从1

insert 
     into c (id, time1, place, xyz, abc, pqrs) 
     (
      select id1 
       , time1 
       , place 
       , xyz 
       , abc 
       , pqrs 
       from (
         select a1.id id1 
          , b1.id id2 
          , CASE b1.time1 > a1.time1 then b1.time1 else a1.time1 end time1 
          , CASE b1.time1 > a1.time1 then b1.place else a1.place end place 
          , CASE b1.time1 > a1.time1 then b1.xyz  else a1.xyz  end xyz 
          , CASE b1.time1 > a1.time1 then b1.abc  else a1.abc  end abc 
          , CASE b1.time1 > a1.time1 then b1.pqrs else a1.pqrs end pqrs 
          from a a1 
        left join b b1 on (b1.id = a1.id) 
         union 
         select b2.id id1 
          , a2.id id2 
          , b2.time1 
          , b2.xyz 
          , b2.place 
          , b2.abc 
          , b2.pqrs          , 
          from b b2 
        left join a a2 on (a2.id = b2.id) 
         where a2.id is null 
        ) pairs 
     ) 
     ;

答

记录在一个请求：

insert into C 
    -- Outer join on B: if B is null null or A has greater time take A 
    select case when B.id is null or A.time1 > B.time1 then A.id else B.id end, 
     case when B.id is null or A.time1 > B.time1 then A.time1 else B.time1 end, 
     case when B.id is null or A.time1 > B.time1 then A.place else B.place end, 
     case when B.id is null or A.time1 > B.time1 then A.xyz else B.xyz end, 
     case when B.id is null or A.time1 > B.time1 then A.abc else B.abc end, 
     case when B.id is null or A.time1 > B.time1 then A.pqrs else B.pqrs end 
    from A, B 
    where B.id (+) = A.id 
union all 
    -- Then get the ones in B and not in A with outer join on A 
    select B.id, B.time1, B.place, B.xyz, B.abc, B.pqrs 
    from B, A 
    where A.id (+) = B.id 
    and A.is is null

答

CREATE TABLE t_c 
(
    id_c NUMBER 
, time_c DATE 
); 

INSERT 
WHEN id_a IS NULL THEN INTO t_c(id_c, time_c) VALUES (id_b, time_b) 
WHEN id_b IS NULL THEN INTO t_c(id_c, time_c) VALUES (id_a, time_a) 
WHEN id_a = id_b AND time_a > time_b THEN INTO t_c(id_c, time_c) VALUES (id_a, time_a) 
WHEN id_a = id_b AND time_a <= time_b THEN INTO t_c(id_c, time_c) VALUES (id_b, time_b) 
SELECT * 
FROM 
(
     SELECT 1 id_a, DATE'2013-01-01' time_a FROM dual UNION ALL 
     SELECT 2 id_a, DATE'2013-01-02' time_a FROM dual UNION ALL 
     SELECT 3 id_a, DATE'2013-01-03' time_a FROM dual UNION ALL 
     SELECT 4 id_a, DATE'2013-01-04' time_a FROM dual UNION ALL 
     SELECT 7 id_a, DATE'2013-01-07' time_a FROM dual UNION ALL 
     SELECT 8 id_a, DATE'2013-01-08' time_a FROM dual 
) t_a 
FULL JOIN 
(
     SELECT 1 id_b, DATE'2013-01-01' time_b FROM dual UNION ALL 
     SELECT 2 id_b, DATE'2013-01-02' time_b FROM dual UNION ALL 
     SELECT 5 id_b, DATE'2013-01-05' time_b FROM dual UNION ALL 
     SELECT 6 id_b, DATE'2013-01-06' time_b FROM dual UNION ALL 
     SELECT 7 id_a, DATE'2013-01-09' time_a FROM dual UNION ALL 
     SELECT 8 id_a, DATE'2013-01-01' time_a FROM dual 
) t_b 
ON  t_a.id_a = t_b.id_b 
; 
/* 
1 2013-01-01 00:00:00  1 2013-01-01 00:00:00 
2 2013-01-02 00:00:00  2 2013-01-02 00:00:00 
          5 2013-01-05 00:00:00 
          6 2013-01-06 00:00:00 
7 2013-01-07 00:00:00  7 2013-01-09 00:00:00 
8 2013-01-08 00:00:00  8 2013-01-01 00:00:00 
4 2013-01-04 00:00:00  
3 2013-01-03 00:00:00  
*/ 

SELECT * 
FROM t_c; 
/* 
5 2013-01-05 00:00:00 
6 2013-01-06 00:00:00 
4 2013-01-04 00:00:00 
3 2013-01-03 00:00:00 
8 2013-01-08 00:00:00 
1 2013-01-01 00:00:00 
2 2013-01-02 00:00:00 
7 2013-01-09 00:00:00 
*/

答

使用UNION，你可以尝试这样的事：

INSERT INTO C 
SELECT A.* 
FROM A 
LEFT OUTER JOIN B ON A.ID = B.ID 
WHERE A.TIME1 > B.TIME1 OR B.ID IS NULL 
UNION 
SELECT B.* 
FROM B 
LEFT OUTER JOIN A ON B.ID = A.ID 
WHERE B.TIME1 > A.TIME1 OR A.ID IS NULL

SQLFIDDLE：http://www.sqlfiddle.com/#!4/5019b/2/0

两个表中合并记录到第三个使用SQL

相关推荐