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 是有效的操作吗？

Java正则表达式，简单
我正在努力实现以下目标，假设我有字符串: ( z ) ( A ( z ) ( A ( z ) ( A ( z ) ( A ( z ) ( A ) ) ) ) ) 我想编写一个正则
CSS水平滚动(简单)
给定: 1 2 3 4 5 6
MySQL填充样例数据(简单)
很难说出这里要问什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或夸夸其谈，无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开，visit the help center . 关闭 1
简单、好懂的Svelte实现原理
大家好，我卡颂。 Svelte问世很久了，一直想写一篇好懂的原理分析文章，拖了这么久终于写了。本文会围绕一张流程图和两个Demo讲解，正确的食用方式是用电脑打开本文，跟着流程图、Demo一
Javascript使用正则验证身份证号(简单)
身份证为15位或者18位，15位的全为数字，18位的前17位为数字，最后一位为数字或者大写字母”X“。与之匹配的正则表达式： ?
强大的jquery插件jqeuryUI做网页对话框效果！简单
我们先来最简单的，网页的登录窗口；不过开始之前，大家先下载jquery的插件本人习惯用了vs2008来做网页了，先添加一个空白页这是最简单的的做法。。。先在body里面插入 <
简单、易用的MySQL官方压力测试工具
1、MySQL自带的压力测试工具 Mysqlslap mysqlslap是mysql自带的基准测试工具,该工具查询数据,语法简单,灵活容易使用.该工具可以模拟多个客户端同时并发的向服务器发出
.NET开源、简单、实用的数据库文档生成工具
前言今天大姚给大家分享一款.NET开源（MIT License）、免费、简单、实用的数据库文档（字典）生成工具，该工具支持CHM、Word、Excel、PDF、Html、XML、Markdown等
【Go基础入门教程】Go语言代码风格清晰、简单
Go语言语法类似于C语言，因此熟悉C语言及其派生语言（ C++、 C#、Objective-C 等）的人都会迅速熟悉这门语言。 C语言的有些语法会让代码可读性降低甚至发生歧义。Go语言在C语言的
FFMpeg 简单/快速转换视频文件不适用于任何视频
我正在使用快速将 mkv 转换为 mp4 ffmpeg 命令 ffmpeg -i test.mkv -vcodec copy -acodec copy new.mp4 但不适用于任何 mkv 文件，当
VBA 计算具有特定名称的工作表数(简单)
我想计算我的工作簿中的工作表数量，然后从总数中减去特定的工作表。我错过了什么？这给了我一个对象错误: wsCount = ThisWorkbook.Sheets.Count - ThisWorkboo
Perl 配置::简单
我有一个 perl 文件，用于查看文件夹中是否存在 ini。如果是，它会从中读取，如果不是，它会根据我为它制作的模板创建一个。我在 ini 部分使用 Config::Simple。我的问题是，如果
ios - 如何访问iOS通知中传递的数据(简单)？
尝试让一个 ViewController 通过标准 Cocoa 通知与另一个 ViewController 进行通信。编写了一个简单的测试用例。在我最初的 VC 中，我将以下内容添加到 viewDi
optimization - (简单？)折线图的标签放置
我正在绘制高程剖面图，显示沿路径的高程增益/损失，类似于下面的: Sample Elevation Profile with hand-placed labels http://img38.image
javascript - 使用JS隐藏div(简单)
嗨，所以我需要做的是最终让 regStart 和 regPage 根据点击事件交替可见性，我不太担心编写 JavaScript 函数，但我根本无法让我的 regPage 首先隐藏。这是我的代码。请简单
c++ - 简单 for 循环中的大量时间损失
我有一个非常简单的程序来测量一个函数花费了多少时间。 #include #include #include struct Foo { void addSample(uint64_t s)
JavaScript 简单 BitConverter
我需要为 JavaScript 制作简单的 C# BitConverter。我做了一个简单的BitConverter class BitConverter{ constructor(){} GetBy
javascript - 简单 for 循环出现意外标记错误？
已关闭。这个问题是 not reproducible or was caused by typos 。目前不接受答案。这个问题是由拼写错误或无法再重现的问题引起的。虽然类似的问题可能是 on-top
group-by - 简单.数据分组依据
我是 Simple.Data 的新手。但我很难找到如何进行“分组依据”。我想要的是非常基本的。表格看起来像: +________+ | cards | +________+ | id |
Javascript 简单 UDF
我现在正在开发一个 JS UDF，它看起来遵循编码。通常情况下，由于循环计数为 2，Alert Msg 会出现两次。我想要的是即使循环计数为 3，Alert Msg 也只会出现一次。任何想法都

行者123

个人简介

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

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

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