SQL查询 - 选择查询 [英] SQL Query - Select Query

问题描述

这是我的数据库关系：

显示（showID，title，premiere_year，network，creator，category）

剧集（showID，episodeID，airdate，title）

• showID是要显示的外键

actor（actID，fname，lname）

main_cast（showID，actID，role）

• showID是要显示的外键actID是actor的外键

recurring_cast（showID，episodeID，actID，role）

• showID是要显示的外键，episodeID是外键
  剧集actID是演员的外键

客户（custID，fname，lname，电子邮件，信用卡，会员， ，password，username）

cust_queue（custID，showID，datequeued）

• custID是客户的外键showID是要显示的外键

已观看（custID，showID，episodeID，datewatched）

• custID是客户的外键


• showID是要显示的外键


• （showID，episodeID）是剧集的外键


• （custID，showID）是cust_queue的外键

所有的'ID'都是主键

---

给了和一些，我不知道如何去。
例如：

查找所有演员至少一个节目的主演员和至少一个节目的循环演员。显示演员的名字，姓氏，演员在主演员中的演出的标题，演员在演奏演员中的演出的标题以及演员在每场演出中扮演的角色。 / p>

我在尝试：

  {
 SELECT演员.fname，Actor.lname，Shows.Title 
 FROM Actor，Shows，Main_Cast，Recurring_Cast 
 WHERE Actor.actID = Main_Cast.actID AND Actor.actID = Recurring_Cast.actID; 
} 
  

但我不认为这是对的。任何想法

解决方案

尝试以下操作：

  SELECT Actor.fname，Actor.lname，Shows.Title 
 FROM Actor，显示AS sh 
 WHERE Actor.actID 
 IN 
（SELECT actID FROM Main_Cast 
 WHERE sh.showID == Main_Cast.showID）
 UNION 
（SELECT actID FROM Recurring_Cast 
 WHERE sh.showID == Recurring_Cast.showID）
  

这将显示在同一节目的主演员和重复演员中的演员。你可以编辑它一点，以得到你想要的。

Here are my database relations:

shows(showID, title, premiere_year, network, creator, category)

episode(showID, episodeID, airdate, title)

• showID is a foreign key to shows

actor(actID, fname, lname)

main_cast(showID, actID, role)

• showID is a foreign key to shows actID is a foreign key to actor

recurring_cast(showID, episodeID, actID, role)

• showID is a foreign key to shows episodeID is a foreign key to episode actID is a foreign key to actor

customer(custID, fname, lname, email, creditcard,membersince,renewaldate, password, username)

cust_queue(custID, showID, datequeued)

• custID is a foreign key to customer showID is a foreign key to shows

watched(custID, showID, episodeID, datewatched)

• custID is a foreign key to customer
• showID is a foreign key to shows
• (showID, episodeID) is a foreign key to episode
• (custID, showID) is a foreign key to cust_queue

All the 'IDs' are primary keys

---

I have queries I was given and for some, I don't know how to go about it. Such as:

Find all actors who are in the main cast of at least one show and in the recurring cast of at least one show. Display the actor's first name, last name, the title of the show in which the actor is in the main cast, the title of the show in which the actors is in the recurring cast, and the role the actor plays in each show.

I'm trying:

{
    SELECT Actor.fname, Actor.lname, Shows.Title
    FROM Actor, Shows, Main_Cast, Recurring_Cast
    WHERE Actor.actID = Main_Cast.actID AND Actor.actID = Recurring_Cast.actID;
}

But I don't think that's right. Any ideas??

解决方案

Try the following:

SELECT Actor.fname, Actor.lname, Shows.Title
FROM Actor, Shows  AS sh
WHERE Actor.actID 
IN 
      (SELECT actID FROM Main_Cast
      WHERE sh.showID==Main_Cast.showID)
UNION
      (SELECT actID FROM Recurring_Cast
      WHERE sh.showID==Recurring_Cast.showID)

This will show you the actors who are in the main cast and recurring cast of the same show. You can edit it a little to get what you want.

这篇关于SQL查询 - 选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！