在 PL/SQL 中合并两个表中的两列

Question

我在尝试合并 PL/SQL 中两个表中的两列时遇到了巨大的困难。我从早上 9:00 就开始做这件事，但我放弃了，请帮助我。

目的

我想创建一个新表（称之为临时表）。这会合并两个不同表中其他两列的信息。

到目前为止的代码

CREATE TABLE temp
    (
        temptimeKey           CHAR(24) NOT NULL ,
        temptimeHour          INTEGER NULL ,
        temptimeMinute        INTEGER NULL ,
        temptimeSecond        INTEGER NULL ,
        temptimeMonth         INTEGER NULL ,
        temptimeDay           INTEGER NULL ,
        temptimeYear          INTEGER NULL ,
        temptimeQuarter       INTEGER NULL ,
        CONSTRAINT  XPKTEMPTIME PRIMARY KEY (temptimeKey)
    );

    insert into temp
    SELECT 
        TO_CHAR(busFareDate, 'MM/DD/YYYY HH:MM:SS Q'),
        TO_NUMBER(TO_CHAR(busFareDate, 'HH12')),
        TO_NUMBER(TO_CHAR(busFareDate, 'MI')),
        TO_NUMBER(TO_CHAR(busFareDate, 'SS')), 
        TO_NUMBER(TO_CHAR(busFareDate, 'MM')),
        TO_NUMBER(TO_CHAR(busFareDate, 'DD')),
        TO_NUMBER(TO_CHAR(busFareDate, 'YYYY')),
        TO_NUMBER(TO_CHAR(busFareDate, 'Q'))
    FROM 
        bus_fare
    UNION
    SELECT
        TO_CHAR(trainFareDate, 'MM/DD/YYYY HH:MM:SS Q'),
        TO_NUMBER(TO_CHAR(trainFareDate, 'HH12')),
        TO_NUMBER(TO_CHAR(trainFareDate, 'MI')),
        TO_NUMBER(TO_CHAR(trainFareDate, 'SS')), 
        TO_NUMBER(TO_CHAR(trainFareDate, 'MM')),
        TO_NUMBER(TO_CHAR(trainFareDate, 'DD')),
        TO_NUMBER(TO_CHAR(trainFareDate, 'YYYY')),
        TO_NUMBER(TO_CHAR(trainFareDate, 'Q'))
    FROM 
        train_fare;

    drop table temp cascade constraints;

到目前为止，这段代码只违反了唯一约束：

ERROR at line 1:
ORA-00001: unique constraint (OPS$FNAVA.XPKTEMPTIME) violated

你能发现我做错了什么吗？ （欢迎任何帮助）

Answer 1

试试这个：

insert into temp
SELECT 
    TO_CHAR(busFareDate, 'MM/DD/YYYY HH:MI:SS Q'),
    TO_NUMBER(TO_CHAR(busFareDate, 'HH12')),
    TO_NUMBER(TO_CHAR(busFareDate, 'MI')),
    TO_NUMBER(TO_CHAR(busFareDate, 'SS')), 
    TO_NUMBER(TO_CHAR(busFareDate, 'MM')),
    TO_NUMBER(TO_CHAR(busFareDate, 'DD')),
    TO_NUMBER(TO_CHAR(busFareDate, 'YYYY')),
    TO_NUMBER(TO_CHAR(busFareDate, 'Q'))
FROM (
        SELECT busFareDate 
        FROM bus_fare 
        GROUP BY busFareDate
        UNION
        SELECT trainFareDate 
        FROM train_fare 
        GROUP BY trainFareDate) AS Dates
GROUP BY TO_CHAR(busFareDate, 'MM/DD/YYYY HH:MI:SS Q'),
        TO_NUMBER(TO_CHAR(busFareDate, 'HH12')),
        TO_NUMBER(TO_CHAR(busFareDate, 'MI')),
        TO_NUMBER(TO_CHAR(busFareDate, 'SS')), 
        TO_NUMBER(TO_CHAR(busFareDate, 'MM')),
        TO_NUMBER(TO_CHAR(busFareDate, 'DD')),
        TO_NUMBER(TO_CHAR(busFareDate, 'YYYY')),
        TO_NUMBER(TO_CHAR(busFareDate, 'Q'));

Answer 2

奇怪的是，UNION 应该自动删除重复项。这是在 Oracle DBMS 上吗？

违反 UNIQUE CONSTRAINT 错误是由于您的查询为要插入的表上的键列返回多行所致。

在您的情况下， TO_CHAR(trainFareDate, 'MM/DD/YYYY HH:MM:SS Q') 将被插入到键列中。如果有两行具有相同的 MM/DD/YYYY HH:MM:SS Q，假设您的 DBMS 确实执行“UNION ALL”，您将收到此错误。

您可以向主键添加第二列，可以将其称为“SOURCE”并用“BUS”或“TRAIN”填充它。

或者，您可以对表进行完整的外部联接。

SELECT coalesce(busFareDate, trainFareDate) from
bus_fare FULL OUTER JOIN train_fare ON 
   (bus_fare.busFareDate = train_fare.trainFareDate);

然后使用该日期作为子查询来执行您需要在日期上执行的所有工作。

编辑：

我认为你真正的问题可能来自使用 HH / HH12。

如果你这样做怎么办：

SELECT 
    TO_CHAR(fareDate , 'MM/DD/YYYY HH24:MI:SS Q'),
    TO_NUMBER(TO_CHAR(fareDate , 'HH24')),
    TO_NUMBER(TO_CHAR(fareDate , 'MI')),
    TO_NUMBER(TO_CHAR(fareDate , 'SS')), 
    TO_NUMBER(TO_CHAR(fareDate , 'MM')),
    TO_NUMBER(TO_CHAR(fareDate , 'DD')),
    TO_NUMBER(TO_CHAR(fareDate , 'YYYY')),
    TO_NUMBER(TO_CHAR(fareDate , 'Q'))
 FROM 
    (SELECT coalesce(busFareDate, trainFareDate) fareDate from
     bus_fare FULL OUTER JOIN train_fare ON 
     (bus_fare.busFareDate = train_fare.trainFareDate));

Answer 3

您的巴士票价和时间票价必须具有相同的日期/时间，违反了限制。为什么不尝试添加一个 GUID 列作为临时表的主键并使用 newid() 在联合的每一侧生成值。

Answer 4

尝试在 TO_CHAR() 周围添加 TRIM()，如下所示：

TRIM(TO_CHAR(busFareDate, 'MM/DD/YYYY HH:MI:SS Q'))

我想我记得 TO_CHAR 有时会在您可能意想不到的地方添加空格填充。如果空格填充导致总长度超过 24 个字符，那么当它进入临时表中的目标列时，它将被截断，这会使得看起来有重复，而实际上没有（即，它们是不同的）仅在最后 2 或 3 个字符中被截断）。

或者，将 CHAR(24) 列扩展到更大的列，例如 CHAR(1000)，看看问题是否消失。