如何在Oracle中进行此查询 [英] How to make this query in Oracle

问题描述

我正在实施电影数据库，我在大学里有这个任务。
我有表格：电影，人， movie_people ， awards 。 人和电影都有获奖的ID字段，可以 NULL 。
我不能得到如何使SQL查询，将显示的人，谁只有在有奖的电影中播放的列表。你能帮助我吗？

  CREATE TABLELAB。MOVIE
（MOVIE_IDNUMBER NOT NULL ENABLE，
 TITLEVARCHAR2（219 BYTE）NOT NULL ENABLE，
YEARMADEDATE，
COUNTRYVARCHAR2（40 BYTE），
RUNNINGTIMENUMBER，
LANGUAGE VARCHAR2（40 BYTE），
TAG_IDNUMBER NOT NULL ENABLE，
REVIEW_IDNUMBER，
AWARD_IDNUMBER，
主键（MOVIE_ID）
使用索引PCTFREE 10 INITRANS 2 MAXTRANS 255 
存储（初始65536下一个1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
 PCTINCREASE 0 FREELISTS 1 FREELIST组1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT）
 TABLESPACEUSERSENABLE ，
 FOREIGN KEY（TAG_ID）
 REFERENCESLAB。TAG（TAG_ID）ON DELETE CASCADE ENABLE，
 FOREIGN KEY（REVIEW_ID）
参考LAB。REVIEW（REVIEW_ID）ON DELETE CASCADE ENABLE，
 CONSTRAINTAWARD_IDFOREIGN KEY（AWARD_ID）
 REFERENCESLABAWARD（AWARD_ID）ENABLE 
）分段创建立即
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 
存储（初始65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
 PCTINCREASE 0 FREELISTS 1 FREELIST组1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT）
 TABLESPACEUSERS; 
 
 CREATE TABLELAB。MOVIE_PEOPLE
（MOVIE_IDNUMBER NOT NULL ENABLE，
PEOPLE_IDNUMBER NOT NULL ENABLE，
ROLEVARCHAR2 （60 BYTE）NOT NULL ENABLE，
 FOREIGN KEY（MOVIE_ID）
 REFERENCESLAB。MOVIE（MOVIE_ID）ON DELETE CASCADE ENABLE，
 FOREIGN KEY（PEOPLE_ID ）
参考LAB。PEOPLE（PEOPLE_ID）ON删除CASCADE ENABLE 
）部分创建立即
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 
存储（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）
 TABLESPACEUSERS; 
 
 CREATE TABLELAB。PEOPLE
（PEOPLE_IDNUMBER NOT NULL ENABLE，
FAMILYNAMEVARCHAR2（40 BYTE）NOT NULL ENABLE，
 GIVENNAMEVARCHAR2（40 BYTE）NOT NULL ENABLE，
GENDERCHAR（1 BYTE）NOT NULL ENABLE，
DATEOFBIRTHDATE，
TAG_IDNUMBER，
 AWARD_IDNUMBER，
 PRIMARY KEY（PEOPLE_ID）
使用索引PCTFREE 10 INITRANS 2 MAXTRANS 255 
存储（初始65536下一个1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT）
 TABLESPACEUSERSENABLE，
 FOREIGN KEY（TAG_ID）
 REFERENCESLABTAG（TAG_ID）ON DELETE CASCADE ENABLE，
 FOREIGN KEY（AWARD_ID）
参考LABAWARD（AWARD_ID）ON DELETE CASCADE ENABLE 
）分段创建立即
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 
存储（初始65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
 PCTINCREASE 0 FREELISTS 1 FREELIST组1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT）
 TABLESPACEUSERS; 
 
 CREATE TABLELAB。AWARD
（AWARD_IDNUMBER NOT NULL ENABLE，
TITLEVARCHAR2（40 BYTE）NOT NULL ENABLE，
 YEARDATE NOT NULL ENABLE，
 PRIMARY KEY（AWARD_ID）
使用索引PCTFREE 10 INITRANS 2 MAXTRANS 255 
存储（初始65536下一个1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT）
 TABLESPACEUSERSENABLE 
）分段创建
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 
存储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）
 TABLESPACEUSERS; 
  

解决方案

  WITH Q1 AS（SELECT DISTINCT p.PEOPLE_ID  - 出现在获奖电影中的人
 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 
内部加入人p 
 ON p .PEOPLE_ID = mp.PEOPLE_ID），
 Q2 AS（SELECT DISTINCT p.PEOPLE_ID  - 出现在非获奖电影中的人
 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 
内部加入人p 
在p.PEOPLE_ID = Q1.PEOPLE_ID 
左外部加入Q2 
在Q2.PEOPLE_ID = Q1.PEOPLE_ID 
 WHERE Q2.PEOPLE_ID is NULL 
  

这里我们使用第一个CTE谁出现在电影中赢得了奖和第二次CTE（Q2）找到所有人谁出现在电影，没有赢得奖励。 （BTW - 你的设计有缺陷，因为电影可以赢得多个奖项，但不介意...）。在主要查询中，我们将所有的第一季度的人员和外部的他们加入到第二季度，只保留那些谁出现在第一季但不是在第二季度。

最好的运气。 / p>

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?

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" ;

解决方案

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.

Best of luck.

这篇关于如何在Oracle中进行此查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！