sql - 看似简单的 SQL 查询结果非常复杂 (Oracle)-6ren

sql - 看似简单的 SQL 查询结果非常复杂 (Oracle)

转载作者：行者123 更新时间：2023-12-04 11:03:12

我在这里展示了我遇到的一个问题的简化版本，我在 Oracle 10.2 上工作:

我为 children 管理圣诞礼物，我有一个 child 的 list ，以及一个根据 child 的年龄和性别准备的合适礼物的理论 list 。我还跟踪他们实际收到的礼物:

    drop table actual_presents;
    drop table kids;
    drop table intended_presents;

    create table kids (
           name varchar2(30),
           sex varchar2(1), 
           constraint pk_kids primary key(name),
           constraint kids_sex_check check (sex in ('M', 'F'))
    );

    insert into kids(name, sex) values ('Alice','F');
    insert into kids(name, sex) values ('Bob','M');
    insert into kids(name, sex) values ('Carol','F');
    insert into kids(name, sex) values ('Dave','M');

    commit;

    create table intended_presents (
           age number,
           sex varchar2(1),
           present varchar2(30),
           constraint pk_intended_presents primary key(age, sex, present),
           constraint present_sex_check check (sex in ('M', 'F'))
    );

    insert into intended_presents(age, sex, present) values (5,'F', 'Doll');
    insert into intended_presents(age, sex, present) values (6,'F', 'Poney');
    insert into intended_presents(age, sex, present) values (5,'M', 'Car');
    insert into intended_presents(age, sex, present) values (6,'M', 'Soldier');

    commit;

    create table actual_presents (
           age number,
           kid varchar2(30),
           present varchar2(30),
           constraint pk_actual_presents primary key(age, kid, present),
           constraint fk_ap_k foreign key(kid) references kids(name)
    );

    insert into actual_presents(age, kid, present) values (5, 'Alice', 'Doll');
    insert into actual_presents(age, kid, present) values (6, 'Alice', 'Poney');
    insert into actual_presents(age, kid, present) values (5, 'Bob', 'Car');
    insert into actual_presents(age, kid, present) values (6, 'Bob', 'Soldier');
    insert into actual_presents(age, kid, present) values (5, 'Carol', 'Car');
    insert into actual_presents(age, kid, present) values (6, 'Carol', 'Poney');
    insert into actual_presents(age, kid, present) values (5, 'Dave', 'Soldier');
    insert into actual_presents(age, kid, present) values (6, 'Dave', 'Car');

    commit;

我现在想要提取每个 child 的预期礼物和实际礼物之间的比较。只要每个 child 只收到一份礼物，就很简单:

    select 'Basic query: double join, works well with 1 present per christmas' as remark from dual;
    select k.name, k.sex, ip.age, ip.present as intended_present, ap.present as actual_present
    from kids k, intended_presents ip, actual_presents ap
    where k.name = ap.kid
    and k.sex = ip.sex
    and ap.age = ip.age
    order by k.name, ip.age, ip.present;

但是还有更复杂的情况:我们可以为给定的圣诞节安排几份礼物，或者应该收到 2 份礼物的 child 实际上可能会收到 1 或 3 份。

    insert into intended_presents(age, sex, present) values (5,'F', 'Drum');
    insert into intended_presents(age, sex, present) values (6,'F', 'Orange');
    insert into intended_presents(age, sex, present) values (5,'M', 'Itchy sweater');
    insert into intended_presents(age, sex, present) values (6,'M', 'Coloring book');        
    insert into intended_presents(age, sex, present) values (7,'M', 'Trumpet');
    insert into intended_presents(age, sex, present) values (7,'M', 'Apple');

    insert into actual_presents(age, kid, present) values (5, 'Alice', 'Drum');
    insert into actual_presents(age, kid, present) values (6, 'Alice', 'Orange');
    insert into actual_presents(age, kid, present) values (5, 'Bob', 'Itchy sweater');
    insert into actual_presents(age, kid, present) values (6, 'Bob', 'Coloring book');
    insert into actual_presents(age, kid, present) values (5, 'Carol', 'Trumpet');
    insert into actual_presents(age, kid, present) values (5, 'Dave', 'Coloring book');
    insert into actual_presents(age, kid, present) values (6, 'Dave', 'Itchy sweater');
    insert into actual_presents(age, kid, present) values (6, 'Dave', 'Puppy');

现在我发现不可能进行查询/ View 返回以下预期结果:

    NAME                   S    AGE ACTUAL_PRESENT         INTENDED_PRESENT
    ------------------------------ - ---------- ------------------------------ ------------------------------
    Alice                  F      5 Doll               Doll
    Alice                  F      5 Drum               Drum
    Alice                  F      6 Orange             Orange
    Alice                  F      6 Poney              Poney
    Bob                M      5 Car                Car
    Bob                M      5 Itchy sweater          Itchy sweater
    Bob                M      6 Coloring book          Coloring book
    Bob                M      6 Soldier            Soldier
    Carol                  F      5 Car                Doll
    Carol                  F      5 Trumpet            Drum
    Carol                  F      6                Orange
    Carol                  F      6 Poney              Poney
    Dave                   M      5 Coloring book          Car
    Dave                   M      5 Soldier            Itchy sweater
    Dave                   M      6 Car                Coloring book
    Dave                   M      6 Itchy sweater          Soldier
    Dave                   M      6 Puppy

    17 rows selected.

前面描述的“简单”查询将返回多余的不需要的行，因为连接不再包含几个礼物(例如 Alice 5 Doll Doll 正确行 1 行，Alice 5 Doll Drum 1 行不正确)。

我最接近有效结果的是通过对行进行排名使用一些技巧，然后尝试仅提取正确的行，但仍然不适合预期的礼物列:

    select 'score + partition by rank' as remark from dual;
    select name, sex, age, actual_present, intended_present from (
           select name, sex, age, actual_present, intended_present, row_number() over (partition by name, sex, age, actual_present order by score desc) as rank
           from (
                select 
                (case when sq.actual_present = ip.present then 1
                else 0
                end) as score,
                sq.name as name, nvl(sq.sex, ip.sex) as sex, nvl(sq.age, ip.age) as age, sq.actual_present, ip.present as intended_present
                from (
                     select k.name, k.sex, ap.age, ap.present as actual_present
                     from kids k,actual_presents ap
                     where k.name = ap.kid
                ) sq
                full outer join intended_presents ip
                on ip.age = sq.age
                and ip.sex = sq.sex
                where name is not null
                )
    ) where rank = 1
    order by name, age, actual_present;

    NAME                   S    AGE ACTUAL_PRESENT         INTENDED_PRESENT
    ------------------------------ - ---------- ------------------------------ ------------------------------
    Alice                  F      5 Doll               Doll
    Alice                  F      5 Drum               Drum
    Alice                  F      6 Orange             Orange
    Alice                  F      6 Poney              Poney
    Bob                M      5 Car                Car
    Bob                M      5 Itchy sweater          Itchy sweater
    Bob                M      6 Coloring book          Coloring book
    Bob                M      6 Soldier            Soldier
    Carol                  F      5 Car                Doll
    Carol                  F      5 Trumpet            *Doll*
    Carol                  F      6 Poney              Poney
    Dave                   M      5 Coloring book          Car
    Dave                   M      5 Soldier            *Car*
    Dave                   M      6 Car                Coloring book
    Dave                   M      6 Itchy sweater          *Coloring book*
    Dave                   M      6 Puppy              *Coloring book*

    16 rows selected.

我尝试添加一个额外的列 present_index 以使连接自然，但这并不能解决问题，因为:
- 仅针对此 View 对数据模型进行了不必要的更改
- 它没有准确地描述现实(1 - 娃娃 + 2 - 鼓相当于 1 - 鼓 + 2 - 娃娃)
- 我们缺少额外礼物的行(Dave - 6 - Puppy - NULL)

    alter table intended_presents add present_index number;
    update intended_presents set present_index = 1 where present in ('Doll', 'Car', 'Orange', 'Soldier', 'Apple');
    update intended_presents set present_index = 2 where present_index is null;
    commit;

    alter table intended_presents drop constraint pk_intended_presents;
    alter table intended_presents add constraint pk_intended_presents primary key(age, sex, present_index);
    select * from intended_presents order by age, sex, present_index;

    alter table actual_presents add present_index number;
    update actual_presents set present_index = 1 where present in ('Doll', 'Car', 'Orange', 'Soldier');
    update actual_presents set present_index = 3 where present in ('Puppy');
    update actual_presents set present_index = 2 where present_index is null;
    commit;
    alter table actual_presents drop constraint pk_actual_presents;
    alter table actual_presents add constraint pk_actual_presents primary key(age, kid, present_index);

    select 'With present_index, we can use that in the join.' as remark from dual;
    select k.name, k.sex, ip.age, ip.present as intended_present, ap.present as actual_present, ap.present_index
    from kids k, intended_presents ip, actual_presents ap
    where k.name = ap.kid
    and k.sex = ip.sex
    and ap.age = ip.age
    and ap.present_index = ip.present_index
    order by k.name, ip.age, ap.present_index;

    NAME                   S    AGE INTENDED_PRESENT           ACTUAL_PRESENT         PRESENT_INDEX
    ------------------------------ - ---------- ------------------------------ ------------------------------ -------------
    Alice                  F      5 Doll               Doll                       1
    Alice                  F      5 Drum               Drum                       2
    Alice                  F      6 Orange             Orange                     1
    Alice                  F      6 Poney              Poney                      2
    Bob                M      5 Car                Car                        1
    Bob                M      5 Itchy sweater          Itchy sweater                  2
    Bob                M      6 Soldier            Soldier                    1
    Bob                M      6 Coloring book          Coloring book                  2
    Carol                  F      5 Doll               Car                        1
    Carol                  F      5 Drum               Trumpet                    2
    Carol                  F      6 Poney              Poney                      2
    Dave                   M      5 Car                Soldier                    1
    Dave                   M      5 Itchy sweater          Coloring book                  2
    Dave                   M      6 Soldier            Car                        1
    Dave                   M      6 Coloring book          Itchy sweater                  2

    15 rows selected.


    update actual_presents set present_index = 3 where kid = 'Alice' and present = 'Doll'; 
    update actual_presents set present_index = 1 where kid = 'Alice' and present = 'Drum'; 
    update actual_presents set present_index = 2 where kid = 'Alice' and present = 'Doll'; 
    commit;

    select k.name, k.sex, ip.age, ip.present as intended_present, ap.present as actual_present, ap.present_index
    from kids k, intended_presents ip, actual_presents ap
    where k.name = ap.kid
    and k.sex = ip.sex
    and ap.age = ip.age
    and ap.present_index = ip.present_index
    order by k.name, ip.age, ap.present_index;

    NAME                   S    AGE INTENDED_PRESENT           ACTUAL_PRESENT         PRESENT_INDEX
    ------------------------------ - ---------- ------------------------------ ------------------------------ -------------
    Alice                  F      5 *Doll*             *Drum*                     1
    Alice                  F      5 *Drum*             *Doll*                     2
    Alice                  F      6 Orange             Orange                     1
    Alice                  F      6 Poney              Poney                      2
    Bob                M      5 Car                Car                        1
    Bob                M      5 Itchy sweater          Itchy sweater                  2
    Bob                M      6 Soldier            Soldier                    1
    Bob                M      6 Coloring book          Coloring book                  2
    Carol                  F      5 Doll               Car                        1
    Carol                  F      5 Drum               Trumpet                    2
    Carol                  F      6 Poney              Poney                      2
    Dave                   M      5 Car                Soldier                    1
    Dave                   M      5 Itchy sweater          Coloring book                  2
    Dave                   M      6 Soldier            Car                        1
    Dave                   M      6 Coloring book          Itchy sweater                  2

    15 rows selected.

最后，我只设法从基于包的流水线函数创建了一个 View ，该函数实际上通过回溯计算了想要的值(如果需要，我可以发布代码)。
对于这个只有 3 个小 table 的简单问题，这似乎非常复杂且成本高昂。我在这里遗漏了一些明显的东西吗？

***************** 解决方案 https://stackoverflow.com/users/3486433/serpiton *****************

您的查询完美地显示了所有差异，我现在要做的就是添加实际 = 预期的行并且我的问题已解决

    WITH ActualNotIntended AS (
      SELECT name, age, sex, present as actual
           ,  Row_Number() OVER (Partition BY name, age ORDER BY present) as ID
      FROM   (SELECT k.name, a.age, k.sex, a.present
              FROM   kids k
                     INNER JOIN actual_presents a ON k.Name = a.kid
              MINUS
              SELECT k.name, i.age, k.sex, i.present
              FROM   kids k
                     INNER JOIN intended_presents i ON k.sex = i.sex) a
    ), IntendedNotActual AS (
      SELECT name, age, sex, present as intended
           ,  Row_Number() OVER (Partition BY name, age ORDER BY present) as ID
      FROM   (SELECT k.name, i.age, k.sex, i.present
              FROM   kids k
                     INNER JOIN intended_presents i ON k.sex = i.sex
                     INNER JOIN actual_presents a ON a.age = i.age AND k.sex = i.sex
              MINUS
              SELECT k.name, a.age, k.sex, a.present
              FROM   kids k
                     INNER JOIN actual_presents a ON k.Name = a.kid) i
    ), ActualIntended as(
              SELECT k.name, a.age, k.sex, a.present as actual, i.present as intended
              FROM kids k 
              JOIN actual_presents a ON k.name = a.kid
              JOIN intended_presents i ON k.sex = i.sex AND i.age = a.age AND i.present = a.present
    )
    SELECT Coalesce(a.name, i.name) as name
         , Coalesce(a.age, i.age) as age
         , Coalesce(a.sex, i.sex) as sex
         , actual
         , intended
    FROM   ActualNotIntended a
           FULL JOIN IntendedNotActual i 
           ON a.name = i.name AND a.age = i.age 
           AND a.sex = i.sex AND a.id = i.id
    UNION 
    SELECT name, age, sex, actual, intended
    FROM ActualIntended
    ORDER by name, age, actual;

    NAME                      AGE S ACTUAL             INTENDED
    ------------------------------ ---------- - ------------------------------ ------------------------------
    Alice                   5 F Doll               Doll
    Alice                   5 F Drum               Drum
    Alice                   6 F Orange             Orange
    Alice                   6 F Poney              Poney
    Bob                 5 M Car                Car
    Bob                 5 M Itchy sweater          Itchy sweater
    Bob                 6 M Coloring book          Coloring book
    Bob                 6 M Soldier            Soldier
    Carol                   5 F Car                Doll
    Carol                   5 F Trumpet            Drum
    Carol                   6 F Poney              Poney

    NAME                      AGE S ACTUAL             INTENDED
    ------------------------------ ---------- - ------------------------------ ------------------------------
    Carol                   6 F                Orange
    Dave                    5 M Coloring book          Car
    Dave                    5 M Soldier            Itchy sweater
    Dave                    6 M Car                Coloring book
    Dave                    6 M Itchy sweater          Soldier
    Dave                    6 M Puppy

    17 rows selected.

作为记录，这比我想要的基于函数的 View 更简单、可读和可维护:

    DROP TYPE V_RECORD_TABLE;
    DROP TYPE V_RECORD;
    CREATE OR REPLACE TYPE V_RECORD AS OBJECT (
        NAME             VARCHAR2(30),
        SEX              VARCHAR2(1),
        AGE              NUMBER,
        ACTUAL_PRESENT   VARCHAR2(30),
        INTENDED_PRESENT VARCHAR2(30) 
        );
    /
    CREATE OR REPLACE TYPE V_RECORD_TABLE AS TABLE OF V_RECORD;
    /


    CREATE OR REPLACE PACKAGE INTENDED_VS_ACTUAL AS 
      FUNCTION GET_DATA RETURN V_RECORD_TABLE PIPELINED;
    END INTENDED_VS_ACTUAL;
    /

    CREATE OR REPLACE PACKAGE BODY INTENDED_VS_ACTUAL AS

      TYPE REF_CURSOR IS REF CURSOR;
      /* types to record intended presents */
      TYPE LIST_OF_IP_REC IS TABLE OF VARCHAR2(30);
      TYPE LIST_OF_IP_PER_AGE IS TABLE OF LIST_OF_IP_REC INDEX BY PLS_INTEGER; /* index per age */
      TYPE LIST_OF_IP IS TABLE OF LIST_OF_IP_PER_AGE INDEX BY VARCHAR2(1);  /* index per sex */

      TYPE EXPECTED_USED IS TABLE OF NUMBER INDEX BY VARCHAR2(30);


    FUNCTION TREAT_PENDING(PENDING_ACTUAL IN OUT LIST_OF_IP_REC, EXP_USED IN OUT EXPECTED_USED, CUR_KID IN VARCHAR2, CUR_AGE IN NUMBER, CUR_SEX VARCHAR2) RETURN V_RECORD_TABLE 
      IS 
      CUR_EXPECTED VARCHAR2(30) := '';
      CUR_EXPECTED_SELECTED VARCHAR2(30) := '';  
      V_REC V_RECORD_TABLE := V_RECORD_TABLE();
      BEGIN
          IF PENDING_ACTUAL.LAST > 0 THEN
             DBMS_OUTPUT.PUT_LINE('pending actual has '||PENDING_ACTUAL.LAST||' items');
             FOR i IN PENDING_ACTUAL.FIRST .. PENDING_ACTUAL.LAST
             LOOP
                DBMS_OUTPUT.PUT_LINE('pending actual '||PENDING_ACTUAL(i));
                CUR_EXPECTED_SELECTED := NULL;
                CUR_EXPECTED := EXP_USED.FIRST;
                WHILE CUR_EXPECTED IS NOT NULL
                LOOP
                    DBMS_OUTPUT.PUT_LINE('checking exp used item '||CUR_EXPECTED||' with value '||EXP_USED(CUR_EXPECTED));
                    IF EXP_USED(CUR_EXPECTED) = 0 THEN
                       EXP_USED(CUR_EXPECTED) := 1;
                       CUR_EXPECTED_SELECTED := CUR_EXPECTED;
                    END IF;
                    EXIT WHEN CUR_EXPECTED_SELECTED IS NOT NULL;
                    CUR_EXPECTED := EXP_USED.NEXT(CUR_EXPECTED);
                END LOOP; -- loop on expected used
                IF CUR_EXPECTED_SELECTED IS NOT NULL THEN 
                   DBMS_OUTPUT.PUT_LINE('returning row with actual '||PENDING_ACTUAL(i)||' exp used selected '||CUR_EXPECTED_SELECTED);
                   V_REC.EXTEND;
                   V_REC(V_REC.LAST) := V_RECORD(CUR_KID, CUR_SEX, CUR_AGE, PENDING_ACTUAL(i), CUR_EXPECTED_SELECTED);       
                ELSE
                   DBMS_OUTPUT.PUT_LINE('returning row with actual '||PENDING_ACTUAL(i)||' exp used selected NULL');
                   V_REC.EXTEND;
                   V_REC(V_REC.LAST) := V_RECORD(CUR_KID, CUR_SEX, CUR_AGE, PENDING_ACTUAL(i), NULL);       
                END IF;
             END LOOP; -- loop on pending actual
          END IF;     -- if pending actual

          /* remaining unused expected */
          DBMS_OUTPUT.PUT_LINE('checking remaining unused expected');
          CUR_EXPECTED := EXP_USED.FIRST;
          WHILE CUR_EXPECTED IS NOT NULL
          LOOP
    --        DBMS_OUTPUT.PUT_LINE('checking exp used item '||CUR_EXPECTED||' with value '||EXP_USED(CUR_EXPECTED));
            IF EXP_USED(CUR_EXPECTED) = 0 THEN
               EXP_USED(CUR_EXPECTED) := 1;
               V_REC.EXTEND;
               V_REC(V_REC.LAST) := V_RECORD(CUR_KID, CUR_SEX, CUR_AGE, NULL, CUR_EXPECTED);       
               DBMS_OUTPUT.PUT_LINE('returning row with actual NULL exp used selected '||CUR_EXPECTED);
            END IF;
            CUR_EXPECTED := EXP_USED.NEXT(CUR_EXPECTED);
          END LOOP; -- loop on expected used
          RETURN V_REC;
      END TREAT_PENDING;


      FUNCTION GET_DATA RETURN V_RECORD_TABLE 
      PIPELINED
      IS
      V_REC V_RECORD_TABLE;

      /* variables to fetch records */
      CUR REF_CURSOR;
      CUR_SEX     VARCHAR2(1);
      CUR_AGE     NUMBER;
      CUR_PRESENT VARCHAR2(30);
      CUR_KID     VARCHAR2(30);
      IP_LIST LIST_OF_IP;
      /* TREATMENT VARS */
      PREV_KID VARCHAR2(30) := '';
      PREV_AGE NUMBER := -1;
      PREV_SEX VARCHAR2(1) := '';

      i NUMBER;
      PENDING_ACTUAL LIST_OF_IP_REC := LIST_OF_IP_REC();

      EXP_USED EXPECTED_USED;
      AP_FOUND BOOLEAN := FALSE;
      BEGIN
      /* first record intended presents by age, sex */
      OPEN CUR FOR SELECT SEX, AGE, PRESENT FROM INTENDED_PRESENTS;
      LOOP
        FETCH cur INTO cur_sex, cur_age, cur_present;
        EXIT WHEN CUR%NOTFOUND;
        IF NOT IP_LIST.EXISTS(CUR_SEX) THEN
           IP_LIST(CUR_SEX)(CUR_AGE) := LIST_OF_IP_REC();
        END IF;
        IF NOT IP_LIST(CUR_SEX).EXISTS(CUR_AGE) THEN
           IP_LIST(CUR_SEX)(CUR_AGE) := LIST_OF_IP_REC();
        END IF;
        IP_LIST(CUR_SEX)(CUR_AGE).EXTEND;
        IP_LIST(CUR_SEX)(CUR_AGE)(IP_LIST(CUR_SEX)(CUR_AGE).LAST) := CUR_PRESENT;
      END LOOP;
      CLOSE CUR;
      DBMS_OUTPUT.PUT_LINE('intended presents recorded');

      /* Treat actual presents */
      OPEN CUR FOR SELECT K.NAME, K.SEX, AP.AGE, AP.PRESENT AS ACTUAL_PRESENT 
                   FROM KIDS K, ACTUAL_PRESENTS AP 
                   WHERE K.NAME = AP.KID ORDER BY K.NAME, AP.AGE;
      LOOP
        FETCH CUR INTO CUR_KID, CUR_SEX, CUR_AGE, CUR_PRESENT;
        EXIT WHEN CUR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('********** actual present record kid '||CUR_KID||' sex '||CUR_SEX||' age '||CUR_AGE||' present '||CUR_PRESENT||' *********');
        IF NOT IP_LIST.EXISTS(CUR_SEX) THEN
          RAISE_APPLICATION_ERROR(-1, 'unknown sex '||CUR_SEX);
        END IF;
        IF NOT IP_LIST(CUR_SEX).EXISTS(CUR_AGE) THEN
          RAISE_APPLICATION_ERROR(-2, 'unknown age for sex '||CUR_SEX||': '||CUR_AGE);
        END IF;

        IF (CUR_KID <> PREV_KID OR CUR_AGE <> PREV_AGE) THEN
          DBMS_OUTPUT.PUT_LINE('switching kid or age, from '||PREV_KID||'/'||PREV_AGE||' to '||CUR_KID||'/'||CUR_AGE);
          IF PREV_AGE > -1 THEN
             /* here send rows from collected data */
             DBMS_OUTPUT.PUT_LINE('Checking for pending records...');
             V_REC := TREAT_PENDING(PENDING_ACTUAL, EXP_USED, PREV_KID, PREV_AGE, PREV_SEX);                                        
             DBMS_OUTPUT.PUT_LINE('Found records to return: '||V_REC.LAST);
             IF V_REC.LAST IS NOT NULL THEN
                FOR i IN V_REC.FIRST .. V_REC.LAST
                LOOP
                   PIPE ROW ( V_REC(i));
                END LOOP;
             END IF;
          END IF;     -- if prev_age > 1 i.e. had something before, to ensure not executed on first row

          /* here reinit variables */
          PENDING_ACTUAL.DELETE;
          EXP_USED.DELETE;
        END IF; -- if switch kid / age
        PREV_KID := CUR_KID;
        PREV_AGE := CUR_AGE;    
        PREV_SEX := CUR_SEX;
        /* Check whether actual present has been returned, store in pending list if not */
        AP_FOUND := FALSE;
        FOR i IN IP_LIST(CUR_SEX)(CUR_AGE).FIRST .. IP_LIST(CUR_SEX)(CUR_AGE).LAST
        LOOP
          IF CUR_PRESENT = IP_LIST(CUR_SEX)(CUR_AGE)(i) THEN
            DBMS_OUTPUT.PUT_LINE('match found for '||CUR_PRESENT||' at index '||i||' marking '||IP_LIST(CUR_SEX)(CUR_AGE)(i)||' as used');
            EXP_USED(IP_LIST(CUR_SEX)(CUR_AGE)(i)) := 1;
            PIPE ROW ( V_RECORD( CUR_KID, CUR_SEX, CUR_AGE, CUR_PRESENT, IP_LIST(CUR_SEX)(CUR_AGE)(i) ) );
            AP_FOUND := TRUE;
          ELSE
            IF NOT EXP_USED.EXISTS(IP_LIST(CUR_SEX)(CUR_AGE)(i)) THEN
              EXP_USED(IP_LIST(CUR_SEX)(CUR_AGE)(i)) := 0;
              DBMS_OUTPUT.PUT_LINE(IP_LIST(CUR_SEX)(CUR_AGE)(i)||' marked as not used');
            END IF;
          END IF;
        END LOOP; -- loop of intended presents
        IF NOT AP_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('no match found for '||CUR_PRESENT||' in intended list. storing in pending_actual');
          PENDING_ACTUAL.EXTEND;
          PENDING_ACTUAL(PENDING_ACTUAL.LAST) := cur_present;
        END IF;


      END LOOP; -- loop on actual presents from query

      V_REC := TREAT_PENDING(PENDING_ACTUAL, EXP_USED, CUR_KID, CUR_AGE, CUR_SEX); -- treat remaining data for last kid/age group of rows
      IF V_REC.LAST IS NOT NULL THEN
         FOR i IN V_REC.FIRST .. V_REC.LAST
         LOOP
            PIPE ROW ( V_REC(i));
         END LOOP;
      END IF;

      RETURN;
      END GET_DATA;

    END INTENDED_VS_ACTUAL;
    /


    CREATE OR REPLACE VIEW V_INTENDED_VS_ACTUAL AS SELECT * FROM TABLE(INTENDED_VS_ACTUAL.GET_DATA());
    SELECT * FROM V_INTENDED_VS_ACTUAL ORDER BY NAME, AGE, ACTUAL_PRESENT;

    NAME                   S    AGE ACTUAL_PRESENT         INTENDED_PRESENT
    ------------------------------ - ---------- ------------------------------ ------------------------------
    Alice                  F      5 Doll               Doll
    Alice                  F      5 Drum               Drum
    Alice                  F      6 Orange             Orange
    Alice                  F      6 Poney              Poney
    Bob                M      5 Car                Car
    Bob                M      5 Itchy sweater          Itchy sweater
    Bob                M      6 Coloring book          Coloring book
    Bob                M      6 Soldier            Soldier
    Carol                  F      5 Car                Doll
    Carol                  F      5 Trumpet            Drum
    Carol                  F      6 Poney              Poney
    Carol                  F      6                Orange
    Dave                   M      5 Coloring book          Car
    Dave                   M      5 Soldier            Itchy sweater
    Dave                   M      6 Car                Soldier
    Dave                   M      6 Itchy sweater          Coloring book
    Dave                   M      6 Puppy

最佳答案

我认为您的问题在于您的数据模型。您在 child 餐 table 上没有任何 child 的年龄，因此您只有将 table 绑在一起的性别。因此，您得到的是每个 child 的预期礼物和实际礼物的所有可能组合，因为给给定年龄和性别的 child 准备的礼物数量不一定是他们实际收到的礼物数量。

年龄在 actual_presents 表中根本不合适，这应该只是 child 和礼物之间的联系。您当前的设计将面临的问题是，您无法根据年龄和性别列出每个 child 有资格收到的礼物 list ，因为您与 child 的年龄没有关系，您只有在两个不同的表格中重复了当前的预期目标年龄。如果我了解您的要求，并且您完全可以更改设计，我会建议如下:

CREATE TABLE kids(KidID number, Name varchar2(30), Sex varchar2(1), Age number);
CREATE TABLE presents(PresentID number, TargetAge number, Sex varchar2(1), Present varchar2(30));
CREATE TABLE  actual_presents(ActualPresentId number, KidID number, PresentID number);

这将允许您像这样存储 child 的详细信息:

insert into kids(KidID, Name, Sex, Age) values (1, 'Alice','F', 6);
insert into kids(KidID, Name, Sex, Age) values (2, 'Bob','M', 7);
insert into kids(KidID, Name, Sex, Age) values (3, 'Carol','F', 5);
insert into kids(KidID, Name, Sex, Age) values (4, 'Dave','M', 5);

insert into presents(PresentID, TargetAge, sex, present) values (1, 5,'F', 'Drum');
insert into presents(PresentID, TargetAge, sex, present) values (2, 6,'F', 'Orange');
insert into presents(PresentID, TargetAge, sex, present) values (3, 5,'M', 'Itchy sweater');
insert into presents(PresentID, TargetAge, sex, present) values (4, 6,'M', 'Coloring book');        
insert into presents(PresentID, TargetAge, sex, present) values (5, 7,'M', 'Trumpet');
insert into presents(PresentID, TargetAge, sex, present) values (6, 7,'M', 'Apple');
insert into presents(PresentID, TargetAge, sex, present) values (7, 5,'F', 'Doll');
insert into presents(PresentID, TargetAge, sex, present) values (8, 6,'F', 'Pony');
insert into presents(PresentID, TargetAge, sex, present) values (9, 5,'M', 'Car');
insert into presents(PresentID, TargetAge, sex, present) values (10, 6,'M', 'Soldier');
insert into presents(PresentID, TargetAge, sex, present) values (11, 7,'F', 'Puppy');

insert into actual_presents(ActualPresentID, KidID, PresentID) values (1, 1, 7);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (2, 1, 8);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (3, 2, 9);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (4, 2, 10);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (5, 3, 9);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (6, 3, 8);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (7, 4, 10);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (8, 4, 9);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (9, 1, 1);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (10, 1, 2);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (11, 2, 3);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (12, 2, 4);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (13, 3, 5);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (14, 4, 4);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (15, 4, 3);
insert into actual_presents(ActualPresentID, KidID, PresentID) values (16, 4, 11);

现在你真的需要有两个查询(无论你改变表格结构还是注释，这都适用):一个显示 child 可以根据年龄和性别收到的礼物，一个显示他们实际得到的礼物。

/*Possible presents per child based on gender and age*/
select k.name, k.sex S, k.age KidAge, p.Present
from kids k
inner join presents p
on p.targetage = k.age
and p.sex = k.sex
order by k.name asc;

/*Actual presents received by each child*/
select k.name, k.sex S, k.age KidAge, p.TargetAge, p.present
from Kids k
inner join actual_presents ap
on ap.KidID = k.KidID
inner join presents p
on p.PresentID = ap.PresentID
Order by k.name asc;

那有意义吗？为任何类型或格式错误道歉，当我真正应该工作时，我在 SQL Server 中这样做了!

关于sql - 看似简单的 SQL 查询结果非常复杂 (Oracle)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/23809713/

文章推荐： r - 在 R 中，如何同时将输出输出到文件和控制台？

文章推荐： t-sql - 在流分析工作中可以有多个查询吗？

文章推荐： elixir - 什么是组长

文章推荐： c - 通过错误循环跳过 switch case 是有效的操作吗？

oracle - Oracle 即时客户端和 Oracle 客户端的区别
Oracle 即时客户端和 Oracle 客户端有什么区别？你能给我解释一下吗？谢谢最佳答案 Oracle 客户端带有安装程序和许多可执行文件，例如 sqlplus，tnsping，它是完整而庞大的
oracle - 无需安装 Oracle 客户端即可连接 ORACLE 数据库服务器的替代方案
我正在寻找一个Delphi组件来直接连接到ORACLE数据库服务器，而无需安装oracle客户端。我知道Oracle Data Access (ODAC)来自DevArt 。还有其他组件具有此功能吗
oracle - 使用 Oracle 表作为输入参数编写 Oracle 存储过程
如何编写 Oracle 存储过程，以表 (X) 作为输入参数，并在过程内部使用表 X 来与另一个表 Y 联接？表 X 将包含数千条记录。不希望将表名作为 varchar 传递，然后使用动态 SQL
oracle - 使用 Oracle 表作为输入参数编写 Oracle 存储过程
如何编写 Oracle 存储过程，以表 (X) 作为输入参数，并在过程内部使用表 X 来与另一个表 Y 联接？表 X 将包含数千条记录。不希望将表名作为 varchar 传递，然后使用动态 SQL
oracle - Oracle sql中如何过滤空值
我要过滤COMMENTS属性为空的记录我试过了 SELECT TABLE_NAME, COMMENTS FROM (SELECT TABLE_NAME, COMMENTS FROM (sel
oracle - Oracle 工具的替代下载站点
我要下载 Oracle Instant Client for Linux x86-64 (64-bit)现在有一段时间了。现在我注意到该网站在过去几个月中一直遇到技术问题。要从 Oracle 下载
oracle - 没有安装 Oracle 客户端的 Delphi FireDac + Oracle
有什么方法可以将我的 Delphi 应用程序 (FireDac) 直接连接到 Oracle 数据库？目前可以连接，但需要安装Oracle Client 在 embarcadero 站点 ( http
oracle - where子句中的日期格式-Oracle
我有一张表，其中日期列的数据格式如下:“7/25/2014 12:14:27 AM”。我需要通过放入 where 子句来获取此日期。有人可以建议我该怎么做吗？最佳答案日期(存储在表中)是 repr
oracle - Oracle 中两个事务可以同时修改同一行吗？
如果两个事务试图同时修改同一行会发生什么？通常，一旦行被修改，另一个事务等待直到第一个事务执行提交或回滚。但是，如果他们恰好在同一时刻发送更新请求怎么办？最佳答案答案是否定的。两个事务不能同时修改
oracle - Oracle 模式中具有相同名称的两个索引
我想知道为什么我不能在 Oracle 模式中有两个同名的索引？它抛出一个错误，指出该名称已被使用。我的印象是，由于索引在一个特定的表上，这应该不会导致任何错误，除非我们对同一个表上的两个不同列使用相同
oracle - Oracle sql中的rownum与group by
我需要构建一个查询来按成员和到期日期检索信息组，但我需要为每个成员提供一个序列号.. 例如: 如果成员“A”有 3 条记录要过期，“B”只有 1 条，“C”有 2 条，我需要这样的结果: Number
oracle - Oracle 中的存储过程和独立过程有什么区别？
独立程序 create procedure proc1 ( begin end; ) 存储过程 create package pkg1 ( procedure proc2 begin end; ) 最
oracle - Oracle 中的外键约束问题
在 Oracle 9i 中声明 FK 时遇到问题。我在这里查看了许多关于 SO 和一些在线文档(例如 http://www.techonthenet.com/oracle/foreign_keys/f
oracle - Oracle 序列存储在哪个表空间中？
我和我的同事维护的应用程序在后端有一个 Oracle 数据库。我们正在考虑偶尔以“受限”模式运行应用程序，其中一个数据库表空间设置为只读。我们可以轻松地将必要的表和索引移动到单独的表空间，这些表空间将
oracle - Oracle 的用户定义聚合函数可以定义为用于两列吗？
我想实现一个自定义的回归聚合函数，类似于现有的 REGR_SLOPE . 我要定义的函数需要获取两列作为参数，例如 select T.EMPLOYEE_ID, CUSTOM_REGR_SL
oracle - Oracle 脚本中的错误处理
我已经尝试解决这个问题一段时间了，我认为是时候寻求帮助了。我正在构建一个架构配置脚本，我想添加一些脚本输出和错误处理。这个想法是脚本输出窗口只会向我显示关键消息而没有所有噪音。 Create Temp
oracle - Oracle 批量更新速度慢
在旧的 Oracle 服务器(我被告知是 8i)上使用 JDBC 时，我遇到了一个非常令人困惑和奇怪的问题。我在那里准备了一个表，其中包含大约 10 列、数字、varchars、一个 raw(255)
oracle - Oracle 分区表的唯一索引
我有一张 table Customer_Chronics在 Oracle 11g 中。该表具有三个关键列，如下所示: 分支代码客户 ID 期我已按 branch_code 列表按表分区，现在我进
oracle - oracle 在哪里存储用户定义的异常？
是否有存储用户自定义异常的oracle表？最佳答案没有。与其他变量一样，用户定义的异常在 PL/SQL block 中定义，并且具有 PL/SQL 变量将具有的任何范围。所以，例如 DECLAR
oracle - Oracle 中的序列与标识列
在 oracle 中使用序列并使用 Before insert trigger 自动递增列或使用标识列是否更好，因为它在 Oracle 12 c 中可用？最佳答案无论哪种方式，您都将使用序列。 1

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 看似简单的 SQL 查询结果非常复杂 (Oracle)