在Oracle SQL中将两个表合并为一个

user2217874 发表于 Dev

用户名

我有两个表，每个表都有许多属性。每个属性都有我想要的属性，我试图编写一个查询以获取这些属性，并将它们放入三列中。

表A

LOCATION_NUMBER (varchar2(20Byte)), 
BEGIN (NUMBER (3,0)), 
BEGIN_DEC (NUMBER (4,3)), 
END (NUMBER (3,0)), 
END_DEC (NUMBER (4,3))

表B

LOCNUM (NUMBER (15,0)), 
START (VARCHAR2(6 BYTE)), 
START_POS (NUMBER (5,3)), 
FINISH (VARCHAR2 (6 BYTE)), 
FINISH_POS (NUMBER (5,3)).

我从查询中想要的是一个具有三个属性的表，然后按位置先对这些表进行排序，然后再开始。

表C，

LOCATION, 
START, 
END

这是一个例子：

A：

LOCATION_NUMBER    BEGIN    BEGIN_DEC    END    END_DEC
      I 26          8          0.63       13      0.99
      I 32          144        0          151     0.82
      I 35          22         0.29       55      0.09
      I 35          128        0.1        67      0.9
      I 35          0          0          22      0.28

B：

LOC_NUM            START    START_POS    FINISH  FINSIH_POS
      227           98         0.56       100     0
      35            149        0.25       151     0.55
      96            156        0.23       156     1.26
      11            263        2.56       263     4.52

结果集：

LOCATION       START        END
    26          8.63         13.99
    32          144          151.82
    35          22.29        55.09
    35          128.1        67.9
    35          0            22.28   
    227         98.56        100
    35          149.25       151.55
    96          156.23       157.26
    11          265.56       267.52

我尝试了一些查询，可以从每个表中获得所需的结果，我只需要知道如何将它们组合为一个，以使事情变得更容易，因为我想要该表，因此，每个表都有10个-我不想担心的20多个属性。

Select to_number(trim(regexp_substr(A.LOCATION_NUMBER ,'[^ ]+', 1, 2))) AS LOCATION,
(to_number(trim(A.BEGIN)) + to_number(trim(A.BEGIN_DEC))) AS START,
(to_number(trim(A.END)) + to_number(trim(A.END_DEC))) AS END
FROM A
ORDER BY to_number(trim(regexp_substr(A.LOCATION_NUMBER,'[^ ]+', 1, 2)));

SELECT B.LOC_NUM AS LOCATION,
(to_number(trim(B.START)) + to_number(trim(B.START_POS))) AS START,
(to_number(trim(B.FINISH)) + to_number(trim(B.FINISH_POS))) AS END
FROM B
ORDER BY B.LOCATION, START;

戈登·利诺夫

假设查询工作正常，您只需要union all：

Select to_number(trim(regexp_substr(A.LOCATION_NUMBER ,'[^ ]+', 1, 2))) AS LOCATION,
       to_number(trim(A.BEGIN)) + to_number(trim(A.BEGIN_DEC)) AS START,
       to_number(trim(A.END)) + to_number(trim(A.END_DEC)) AS END
FROM A
UNION ALL
SELECT B.LOC_NUM AS LOCATION,
       to_number(trim(B.START)) + to_number(trim(B.START_POS)) AS START,
       to_number(trim(B.FINISH)) + to_number(trim(B.FINISH_POS)) AS END
FROM B
ORDER BY B.LOCATION, START;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。