如何在Oracle中进行此查询

I'm implementing movies database, I have this task in my university. I have tables: movies, people, movie_people, awards. Both people and movies have field with id of award, which can be NULL. I can't get how to make SQL query which will show list of people, who played only in films which have awards. Can you help me with that?

我正在实施电影数据库,我在大学里有这个任务。我有桌子:电影,人物,电影人,奖项。人物和电影都有id为id的字段,可以为NULL。我无法得到如何进行SQL查询,这将显示仅在有奖项的电影中播放的人员列表。你能帮帮我吗?

CREATE TABLE "LAB"."MOVIE" 
   (    "MOVIE_ID" NUMBER NOT NULL ENABLE, 
    "TITLE" VARCHAR2(219 BYTE) NOT NULL ENABLE, 
    "YEARMADE" DATE, 
    "COUNTRY" VARCHAR2(40 BYTE), 
    "RUNNINGTIME" NUMBER, 
    "LANGUAGE" VARCHAR2(40 BYTE), 
    "TAG_ID" NUMBER NOT NULL ENABLE, 
    "REVIEW_ID" NUMBER, 
    "AWARD_ID" NUMBER, 
     PRIMARY KEY ("MOVIE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
     FOREIGN KEY ("TAG_ID")
      REFERENCES "LAB"."TAG" ("TAG_ID") ON DELETE CASCADE ENABLE, 
     FOREIGN KEY ("REVIEW_ID")
      REFERENCES "LAB"."REVIEW" ("REVIEW_ID") ON DELETE CASCADE ENABLE, 
     CONSTRAINT "AWARD_ID" FOREIGN KEY ("AWARD_ID")
      REFERENCES "LAB"."AWARD" ("AWARD_ID") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

  CREATE TABLE "LAB"."MOVIE_PEOPLE" 
   (    "MOVIE_ID" NUMBER NOT NULL ENABLE, 
    "PEOPLE_ID" NUMBER NOT NULL ENABLE, 
    "ROLE" VARCHAR2(60 BYTE) NOT NULL ENABLE, 
     FOREIGN KEY ("MOVIE_ID")
      REFERENCES "LAB"."MOVIE" ("MOVIE_ID") ON DELETE CASCADE ENABLE, 
     FOREIGN KEY ("PEOPLE_ID")
      REFERENCES "LAB"."PEOPLE" ("PEOPLE_ID") ON DELETE CASCADE ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

  CREATE TABLE "LAB"."PEOPLE" 
   (    "PEOPLE_ID" NUMBER NOT NULL ENABLE, 
    "FAMILYNAME" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
    "GIVENNAME" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
    "GENDER" CHAR(1 BYTE) NOT NULL ENABLE, 
    "DATEOFBIRTH" DATE, 
    "TAG_ID" NUMBER, 
    "AWARD_ID" NUMBER, 
     PRIMARY KEY ("PEOPLE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
     FOREIGN KEY ("TAG_ID")
      REFERENCES "LAB"."TAG" ("TAG_ID") ON DELETE CASCADE ENABLE, 
     FOREIGN KEY ("AWARD_ID")
      REFERENCES "LAB"."AWARD" ("AWARD_ID") ON DELETE CASCADE ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

  CREATE TABLE "LAB"."AWARD" 
   (    "AWARD_ID" NUMBER NOT NULL ENABLE, 
    "TITLE" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
    "YEAR" DATE NOT NULL ENABLE, 
     PRIMARY KEY ("AWARD_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

1 个解决方案

#1

One way to accomplish this would be

实现这一目标的一种方法是

WITH Q1 AS (SELECT DISTINCT p.PEOPLE_ID  -- people who appeared in award-winning movies
              FROM MOVIES m
              INNER JOIN AWARDS a
                ON a.AWARD_ID = m.AWARD_ID
              INNER JOIN MOVIE_PEOPLE mp
                ON mp.MOVIE_ID = m.MOVIE_ID
              INNER JOIN PEOPLE p
                ON p.PEOPLE_ID = mp.PEOPLE_ID),
     Q2 AS (SELECT DISTINCT p.PEOPLE_ID  -- people who appeared in non-award-winning movies
              FROM MOVIES m
              INNER JOIN MOVIE_PEOPLE mp
                ON mp.MOVIE_ID = m.MOVIE_ID
              INNER JOIN PEOPLE p
                ON p.PEOPLE_ID = mp.PEOPLE_ID
              WHERE m.AWARD_ID IS NULL)
SELECT Q1.PEOPLE_ID, p.*
  FROM Q1
  INNER JOIN PEOPLE p
    ON p.PEOPLE_ID = Q1.PEOPLE_ID
  LEFT OUTER JOIN Q2
    ON Q2.PEOPLE_ID = Q1.PEOPLE_ID
  WHERE Q2.PEOPLE_ID IS NULL

Here we use the first CTE (Q1) to find all people who appeared in movies which won an award and the second CTE (Q2) to find all people who appeared in movies which did not win an award. (BTW - your design is flawed as movies can win multiple awards, but never mind...). In the main query we take all people in Q1 and outer join them to Q2, retaining only those people who appear in Q1 but not in Q2.

在这里,我们使用第一个CTE(Q1)找到所有出现在获得奖项的电影中的人和第二个CTE(Q2),以找到所有出现在没有获奖的电影中的人。 (顺便说一下 - 你的设计是有缺陷的,因为电影可以赢得多个奖项,但没关系......)。在主查询中,我们将Q1中的所有人和外部人员连接到Q2,仅保留那些出现在Q1而不是Q2的人。

Best of luck.

祝你好运。

#1