sql - 创建和连接临时表

Question

我正在寻找一些帮助来加入两个临时表以获得最终输出。

我必须创建不同的表，一个来计算花费的时间，另一个计算总和

不幸的是，我无法在一张表中执行此操作，因为有很多重复的时间值导致计算错误。

下面是代码：

---

CREATE TABLE KPI_REPORT (
    USERNAME      CHAR(50),
    HOURS         NUMBER(10),
    MINUTES       NUMBER(10),
    TOTAL_CTN     NUMBER(10),
    AVERAGE_CTN   NUMBER(10)
);

CREATE TABLE TOTAL_TIME (
    USERNAME   CHAR(50),
    HOURS      NUMBER(10),
    MINUTES    NUMBER(10)
);

CREATE TABLE TOTAL_CARTONS (
    USERNAME    CHAR(50),
    TOTAL_CTN   NUMBER(10)
);
    INSERT INTO Total_Time ( Username, Hours, Minutes )
    select 
        ia.username --username
        ,ROUND( SUM( ia.dtime3-ia.dtime1 ) * 24,2 ) --hours
        ,ROUND( SUM( ( ia.dtime3-ia.dtime1 ) * 24 ) * 60,2 ) --minutes
    from 
        actual_db_1 ia
    where 
        to_char( ia.dtime3, 'YYYY-MM-DD' ) between '2019-01-08' and '2019-01-08'
        and ia.site = '7'
        and ia.from_location = 'ORDERPICK'
        and ia.queue like 'LL%'
    group by
        ia.username;

    COMMIT;

    INSERT INTO Total_Cartons ( Username, Total_Ctn )
    select
        ia.USERNAME --username
        ,SUM( pm.qty/pm.packfactor_1 ) --cartons
    from 
        actual_db_1 ia, 
        actual_db_2 pm
    where 
        to_char( ia.dtime3, 'YYYY-MM-DD' ) between '2019-01-08' and '2019-01-08'
        and ia.site = '7'
        and ia.queue like 'LL%'
        and pm.code = product
    group by 
        ia.username;

    COMMIT;

    INSERT INTO KPI_Report ( Username, Hours, Minutes, Total_Ctn, Average_Ctn )

    select
        Total_Time.Username
        ,Total_Time.Hours
        ,Total_Time.Minutes
        ,Total_Cartons.Total_Ctn
        ,ROUND( ( SUM( ia.qty/pm.packfactor_1 ) )  / ( SUM( ia.dtime3-ia.dtime1) * 24 ),2 )
    from 
        Total_Time
        ,Total_Cartons
    where 
        Total_Time.Username = Total_Cartons.Username 
    group by
       Total_Time.Username 
       ,Total_Time.Hours
       ,Total_Time.Minutes
       ,Total_Cartons.Total_Ctn
    order by
        ia.username;

    COMMIT;

---

感谢任何帮助，因为我被困住了。

标签： sqloracletemp-tables

Answer 1

您必须添加表actual_db_1才能actual_db_2查询

CREATE TABLE KPI_REPORT (
    USERNAME      CHAR(50),
    HOURS         NUMBER(10),
    MINUTES       NUMBER(10),
    TOTAL_CTN     NUMBER(10),
    AVERAGE_CTN   NUMBER(10)
);

CREATE TABLE TOTAL_TIME (
    USERNAME   CHAR(50),
    HOURS      NUMBER(10),
    MINUTES    NUMBER(10)
);

CREATE TABLE TOTAL_CARTONS (
    USERNAME    CHAR(50),
    TOTAL_CTN   NUMBER(10)
);
    INSERT INTO Total_Time ( Username, Hours, Minutes )
    select 
        ia.username --username
        ,ROUND( SUM(ia.dtime3-ia.dtime1 ) * 24,2 ) --hours
        ,ROUND( SUM( ( ia.dtime3-ia.dtime1 ) * 24 ) * 60,2 ) --minutes
    from 
        actual_db_1 ia
    where 
        to_char( ia.dtime3, 'YYYY-MM-DD' ) between '2019-01-08' and '2019-01-08'
        and ia.site = '7'
        and ia.from_location = 'ORDERPICK'
        and ia.queue like 'LL%'
    group by
        ia.username;

    COMMIT;

    INSERT INTO Total_Cartons ( Username, Total_Ctn )
    select
        ia.USERNAME --username
        ,SUM( pm.qty/pm.packfactor_1 ) --cartons
    from 
        actual_db_1 ia, 
        actual_db_2 pm
    where 
        to_char( ia.dtime3, 'YYYY-MM-DD' ) between '2019-01-08' and '2019-01-08'
        and ia.site = '7'
        and ia.queue like 'LL%'
        and pm.code = product
    group by 
        ia.username;

    COMMIT;

    INSERT INTO KPI_Report ( Username, Hours, Minutes, Total_Ctn, Average_Ctn )

    select
        Total_Time.Username
        ,Total_Time.Hours
        ,Total_Time.Minutes
        ,Total_Cartons.Total_Ctn
        ,ROUND( ( SUM( ia.qty/pm.packfactor_1 ) )  / ( SUM( ia.dtime3-ia.dtime1) * 24 ),2 )
    from 
        Total_Time
        INNER JOIN Total_Cartons ON Total_Time.Username = Total_Cartons.Username
        INNER JOIN actual_db_1 ia ON ia.username = Total_Time.Username
        INNER JOIN actual_db_2 pm ON ia.product = pm.code

    group by
       Total_Time.Username 
       ,Total_Time.Hours
       ,Total_Time.Minutes
       ,Total_Cartons.Total_Ctn
    order by
        ia.username;

    COMMIT;