![f103cfafef7b616fc434afc8b90c7829.png](https://i-blog.csdnimg.cn/blog_migrate/e55811441a7e3224308c0bcefe25f476.jpeg)
摘要
表的加法#1
表的联结#2
联结应用案例#3
case表达式#4
表的加法#1
集合在数学领域表示“(各种各样的)事物的总和”,在数据库领域表示记录的集合。具体来说,表、视图和查询的执行结果都是记录的集合。
集合运算,就是对满足同一规则的记录进行的加减等四则运算。通过集合运算,可以得到两张表中记录的集合或者公共记录的集合,又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符。
表的加法——使用UNION集合运算符进行记录加法运算,通常都会除去重复的记录。
练习:查询课程号、课程名称
select 课程号,课程名称
from course
union
select 课程号,课程名称
from course1;
![79b23bb51bc8f3ef075b689b74360a5c.png](https://i-blog.csdnimg.cn/blog_migrate/28f4aab13b618af66811b294f807d8c1.png)
利用文氏图有助于理解集合运算以及表的不同类型的联结
![3331815e92e92429bb5bd57dfddaa366.png](https://i-blog.csdnimg.cn/blog_migrate/1375fdc46d86e938294ffd36c9d0596f.jpeg)
若想保留重复的记录,在union后加all
练习:查询课程号、课程名称,保留所有记录
select 课程号,课程名称
from course
union all
select 课程号,课程名称
from course1;
![4a3a9aca0c39e6947807fc01cce0afaf.png](https://i-blog.csdnimg.cn/blog_migrate/2c89dd7f19b60778c9f4b0b1a3a9165c.png)
集合运算的注意事项:
1、作为运算对象的记录的列数必须相同;
2、作为运算对象的记录中列的类型必须一致;
3、可以使用任何SELECT语句,但ORDER BY子句只能在最后使用一次
表的联结#2
各表之间的关系,观察学生表、成绩表、课程表、教师表这4张表是通过什么字段联结起来的,下面的练习来自4张表中的数据
![1d77ed96d2de0329d2c1b5da66b90992.png](https://i-blog.csdnimg.cn/blog_migrate/13c786fdad050ef5a8136e77626b3dc5.jpeg)
![bfa877536b60d746cb04863f123c91c0.png](https://i-blog.csdnimg.cn/blog_migrate/f123587c7253a90b6965e33621438917.jpeg)
联结(join):交叉联结、内联结、左联结、右联结、全联结
![432c4632b78a30b42a061606342207b9.png](https://i-blog.csdnimg.cn/blog_migrate/44878a5ad4e8e62cc076defa2d0bcb3a.jpeg)
![dad02b52c4ac5a353143a0875e72e47f.png](https://i-blog.csdnimg.cn/blog_migrate/3826229326b571c19d984d0c13b85381.jpeg)
交叉联结(笛卡尔积、cross join):结果的行数是两张表中行数的乘积,交叉联结的结果太多,实际操作中较少用到,但交叉联结是所有联结运算的基础。他们是在交叉联结的基础上加了其他过滤条件。
对满足相同规则的表进行交叉联结的集合运算符是CROSS JOIN(笛卡儿积)。进行交叉联结时无法使用内联结和外联结中所使用的ON子句,这是因为交叉联结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积。
内联结是交叉联结的一部分,“内”也可以理解为“包含在交叉联结结果中的部分”。相反,外联结的“外”可以理解为“交叉联结结果之外的部分”。
交叉联结没有应用到实际业务之中的原因有两个。一是其结果没有实用价值,二是由于其结果行数太多,需要花费大量的运算时间和高性能设备的支持。
![0150374e44a2ce6e44e46a3ef0ca6462.png](https://i-blog.csdnimg.cn/blog_migrate/9aeebf9622cd3e7c7916d7130d1c673f.jpeg)
内联结(inner join):内联结的运行顺序是先在联结的表中取出符合条件的行,然后进行交叉联结。
![9681e2f103d0a537fb4491883589ba9f.png](https://i-blog.csdnimg.cn/blog_migrate/d7339cc06930b4023b24857e08c302a8.jpeg)
![f0ef78a7868af609139f6f349bcc44ff.png](https://i-blog.csdnimg.cn/blog_migrate/e4bebfd5979994ad31bc62df76cd3ba5.jpeg)
在使用过程中需要遵循一定的书写规范,不同子句的注意事项如下:
1、关于FROM子句,之前的FROM子句中只有一张表,而这次我们同时使用了student和score两张表。
使用关键字INNER JOIN就可以将两张表联结在一起了。可使用AS为两张表设置别名,虽然别名并不是必需,但由于表名太长会影响SQL语句的可读性,因此应养成使用别名的习惯。
2、关于ON子句,ON后面是联结条件。可以在ON之后指定两张表联结所使用的列(联结键),也就是说,ON是专门用来指定联结条件的,它能起到与WHERE相同的作用。需要指定多个键时,同样可以使用AND、OR。在进行内联结时ON子句是必不可少的(如果没有ON会发生错误),并且ON必须书写在FROM和WHERE之间。联结条件也可以使用“=”来记述。在语法,还可以使用<=和BETWEEN等谓词。实际应用中九成以上都可以用“=”进行联结,使用“=”将联结键关联起来,就能够将两张表中满足相同条件的记录进行“联结”了。
3、关于SELECT子句,在SELECT子句中指定的列。使用“< 表的别名 >.< 列名 >”的形式来指定列。和使用一张表时不同,由于多表联结时,某个列到底属于哪张表比较容易混乱,因此采用了这样的防范措施。
练习:使用inner join联结学生表、成绩表、课程表
-- 查询学生的选课情况:学号,姓名,课程号,课程名称
select a.学号,a.姓名,c.课程号,c.课程名称
from student as a inner join score as b on a.学号=b.学号
inner join course c on b.课程号=c.课程号;
![b1f77051a27d00f4dabca82f6ebd1b46.png](https://i-blog.csdnimg.cn/blog_migrate/14259bd24aa7e8a32dcfe17963fc8608.jpeg)
![b55b7c53356889a7ec0c3e7a3886e281.png](https://i-blog.csdnimg.cn/blog_migrate/286fa0bb7082861e67c0a009dc1f5848.jpeg)
通过上图可以看到学生表、成绩表、课程表的联结键分别是:学生表.学号、成绩表.学号、成绩表.课程号、课程表.课程号,通过这四个联结键把这三张表联结起来。根据内联结的运行顺序,成绩表中的学号与课程号分别与学生表中的学号,课程表中的课程号一一对应,因此,最终输出结果是笛卡尔积:1*8*1=8行数据。
左联结(left join)、右联结(right join):
两个联结均属于外联结(OUTER JOIN),通过ON子句的联结键把两张表进行联结,并从两张表中同时选取相应的列。外联接与内联结的使用方法并没有什么不同,只是结果却有所不同。
在实际的业务中,外联接生成固定行数的数据,如果使用内联结,根据SELECT语句执行时的状况不同,结果的行数也会发生改变,生成的数据的版式也会受到影响,而使用外联结能够得到固定行数的结果。
外联结名称的由来也跟NULL有关,即“结果中包含原表中不存在(在原表之外)的信息”。相反,只包含表内信息的联结也就被称为内联结了。
外联结还有一点非常重要,那就是要把哪张表作为主表。最终的结果中会包含主表内所有的数据。指定主表的关键字是LEFT和RIGHT。顾名思义,使用LEFT时FROM子句中写在左侧的表是主表,使用RIGHT时右侧的表是主表。
练习:以学生表作为主表,联结成绩表
select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号 = b.学号;
![323f548c292a770a496d5aa1c8292128.png](https://i-blog.csdnimg.cn/blog_migrate/4f811a97d2b1287c08b63ac4551dccea.png)
以下为左联结(left join)运行顺序,数据有所删减,但原理一致。
![ee5785d3e23e6f6aaa082c82c8cfd5ed.png](https://i-blog.csdnimg.cn/blog_migrate/c12a16f515e9645731f131571967871a.jpeg)
![ec11d2bb6875797966e0f07cda89da94.png](https://i-blog.csdnimg.cn/blog_migrate/48f156616e7571a50c9c8d9c68a45bf7.jpeg)
练习:以学生表作为主表,联结成绩表,查找成绩表中为空值的行
select a. 学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号 = b.学号
where b.学号 is null;
![f08f8aebb52d319879cd8fc537d01105.png](https://i-blog.csdnimg.cn/blog_migrate/af26fe67464da5b1ccd0ce80904073ba.jpeg)
以下为左联结运行过程,数据有所删减,但原理是一样的,使用where指定成绩表中的联结键‘学号’为空值后,得出的结果如文氏图显示:左边圈不与右边圈重叠的红色部分。
![255970451bdf7433b32c5f4b4776cdc5.png](https://i-blog.csdnimg.cn/blog_migrate/73ca356966e7e28c2f275ce251f60f4c.jpeg)
练习:查询所有学生的学号、姓名、选课数、总成绩,
-- select语句中的学号需要标明是哪个表的,不然无法识别会导致报错
select a.学号,姓名,count(课程号)as 选课数,sum(成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;
![f4877529970c185ddb4503868bf9a56e.png](https://i-blog.csdnimg.cn/blog_migrate/a3d6edc7f83378964a471fa4300e985d.jpeg)
练习:使用right join以成绩表作为主表,查看学号、姓名、及课程号,观察与左联结的区别
select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号=b.学号;
![c6ee3726fffc1eaf185c1078e58d8bd1.png](https://i-blog.csdnimg.cn/blog_migrate/c7773674e6b3bdd9c9bbcf2a960c9a77.png)
![2774bb25b5531d9cf7f4690ce8ce899a.png](https://i-blog.csdnimg.cn/blog_migrate/735093f3198928d72e489de6a47c578c.jpeg)
练习:使用right join以成绩表作为主表,查看学号、姓名、及课程号,使用where指定学生表中的联结键‘学号’为空值,观察与左联结有什么区别
select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号=b.学号
-- student表中没有空值,因此添加“where a.学号 is null”条件后,结果显示空值
where a.学号 is null;
![813168bf1830fc56b9281ccb3073a3f1.png](https://i-blog.csdnimg.cn/blog_migrate/f81c062d66633759f53a605b151444d6.jpeg)
使用where指定学生表中的联结键‘学号’为空值后,得出的结果如文氏图显示:不与左边圈重叠的红色部分。
![49c03a53eab126b8271bd23af3c571ea.png](https://i-blog.csdnimg.cn/blog_migrate/593b6c2f9f69cf75bee28bd419c3d1b1.jpeg)
全联结(full join):得到的结果如文氏图中包括重叠与不重叠的红色部分。
![1bd54a45dff9a0a36ecaefc035b3115d.png](https://i-blog.csdnimg.cn/blog_migrate/1d0bfe888f4a509bc435a2b959961210.jpeg)
通过文氏图理解不同的sql联结模式
![eff2cc979870e4c0b11e1d1e5fe76c24.png](https://i-blog.csdnimg.cn/blog_migrate/72f944a542cf43cfe87792bd1b7e988d.jpeg)
联结应用案例#3
如何用sql解决业务问题,同样是分三步:
1)翻译成大白话
2)写出分析思路
3)写出对应的sql子句
练习:查询所有学生的学号、姓名、选课数、总成绩
1)翻译成大白话
![203a921a690b98a1870a21a0609f37b4.png](https://i-blog.csdnimg.cn/blog_migrate/b973ce9c4ba2772f65b802182d4c98ac.jpeg)
2)写出分析思路
![c248596a5f996b2b6e52788e8b94ef69.png](https://i-blog.csdnimg.cn/blog_migrate/f5c821716e64e3f5c2ef82406112ed44.jpeg)
3)写出对应的sql子句
select s1.学号,s1.姓名,count(课程号)as 选课数,sum(成绩)as 总成绩
from student as s1 left join score as s2 on s1.学号=s2.学号
group by s1.学号;
![38bcf70727c2702ad4e74bc1389eecc3.png](https://i-blog.csdnimg.cn/blog_migrate/f662be4060a94b4ba19744bb6087d324.jpeg)
练习: 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
翻译成大白话并写出分析思路:
1)查询出所有学生的学号,姓名,平均成绩
学号,姓名(在学生表student),
平均成绩(每个学生的平均成绩:在成绩表score,)
按学号分组,平均成绩:avg(成绩)
2)平均成绩>85
写出对应的sql子句:
select a.学号,a.姓名,round(avg(b.成绩),2)as 平均成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号
having avg(b.成绩)>85;
![d6b7275a5c69edea3175281e890752cf.png](https://i-blog.csdnimg.cn/blog_migrate/9a49ba3451d71c54aa29604ac09b001f.png)
练习:查询所有学生的学号、姓名、平均成绩
-- 若没有指定哪个表的学号、姓名、成绩,将会报错
select a.学号,a.姓名,avg(b.成绩)as 平均成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号;
![e88444f998da22e1aecbb7fc44b7e217.png](https://i-blog.csdnimg.cn/blog_migrate/659d245880637f089f9ff959a1afa5f2.jpeg)
练习:查询学生的选课情况:学号、姓名、课程号、课程名称
select s1.学号,s1.姓名,s2.课程号,课程名称
from student as s1 left join score as s2 on s1.学号=s2.学号
left join course as c on s2.课程号=c.课程号
ORDER BY s1.学号,s2.课程号
![a765d1f15590fb8ee34f428ce7aeb0d9.png](https://i-blog.csdnimg.cn/blog_migrate/86165c7fffd0eeff8c71268db7453477.jpeg)
![fe6b359f22b9a048f957492f352fe435.png](https://i-blog.csdnimg.cn/blog_migrate/170d614c311e7bc0b82a288169b19325.jpeg)
以下练习来自SQLZOO《The JOIN operation》:
The JOIN operation/zhsqlzoo.net![f371856dd030c79c69c92dce41f51a20.png](https://i-blog.csdnimg.cn/blog_migrate/443d09485135d516d5f5ac66be3f64d9.jpeg)
SQLZOO练习8:找非德國球員的入球,德國可以在賽事中作team1 隊伍1(主)或team2隊伍2(客)。 你可以用teamid!='GER' 來防止列出德國球員。 你可以用DISTINCT來防止球員出現兩次以上。(使用distinct防止球员出现两次以上,teamid!='GER' 则可以防止列出德国球员)
SELECT distinct(player)
FROM game left JOIN goal ON matchid = id
WHERE teamid!='GER' and
(team1='GER' or team2='GER');
![f834f166a1efe50ef212c052b4959698.png](https://i-blog.csdnimg.cn/blog_migrate/86cd0fc92552bd47d44fc0dd5489740d.png)
SQLZOO练习11:每一場波蘭'POL'有參與的賽事中,列出賽事編號 matchid, 日期date 和入球數字。
SELECT matchid,mdate,count(matchid)
FROM game JOIN goal ON matchid = id
where team1='POL' or team2='POL'
group by matchid,mdate;
![4149e0633c2109d2c3e136b4ee418b8e.png](https://i-blog.csdnimg.cn/blog_migrate/1391ef6e9e17fb80d6d5ff6a9542d5d6.jpeg)
SQLZOO练习12:每一場德國'GER'有參與的賽事中,列出賽事編號 matchid, 日期date 和德國的入球數字。
select matchid,mdate,count(teamid)
from game join goal on id=matchid
where (team1='GER' or team2='GER')
and teamid='GER'
group by matchid,mdate;
![e5b59bbf4028d7c3029f7ec216bcfca7.png](https://i-blog.csdnimg.cn/blog_migrate/d106cedd81814beb460d5261bd284de1.jpeg)
case表达式#4
CASE表达式分为简单CASE表达式和搜索CASE表达式两种。搜索CASE表达式包含简单CASE表达式的全部功能。
虽然CASE表达式中的ELSE子句可以省略,但为了让SQL语句更加容易理解,建议不要省略。
CASE表达式中的END不能省略。使用CASE表达式能够将SELECT语句的结果进行组合。
虽然有些DBMS提供了各自特有的CASE表达式的简化函数,例如Oracle中的DECODE和MySQL中的IF,等等,但由于它们并非通用的函数,功能上也有些限制,因此有些场合无法使用。
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。但是,由于搜索CASE表达式包含了简单CASE表达式的全部功能,因此只需大致了解简单CASE表达式。
ELSE子句也可以省略不写,这时会被默认为ELSE NULL。但为了防止有人漏读,还是希望大家能够显示地写出ELSE子句。
搜索CASE表达式
CASE WHEN <判断表达式> THEN <表达式>
WHEN <判断表达式> THEN <表达式>
WHEN <判断表达式> THEN <表达式>
. . .
ELSE <表达式>END
WHEN子句中的“< 判断表达式 >”就是类似“列=值”这样,返回值为真值(TRUE/FALSE/UNKNOWN)的表达式。我们也可以将其看作使用=、!=或者LIKE、BETWEEN等谓词编写出来的表达式。
CASE表达式会从对最初的WHEN子句中的“< 判断表达式 >”进行求值开始执行。所谓求值,就是要调查该表达式的真值是什么。如果结果为真(TRUE),那么就返回THEN子句中的表达式,CASE表达式的执行到此为止。如果结果不为真,那么就跳转到下一条WHEN子句的求值之中。如果直到最后的WHEN子句为止返回结果都不为真,那么就会返回ELSE中的表达式,执行终止。
从CASE表达式名称中的“表达式”我们也能看出来,上述这些整体构成了一个表达式。并且由于表达式最终会返回一个值,因此CASE表达式在SQL语句执行时,也会转化为一个值。虽然使用分支众多的CASE表达式编写几十行代码的情况也并不少见,但是无论多么庞大的CASE表达式,最后也只会返回单一简单的值。
![d5467ac199f216b539d6bc691136b9fd.png](https://i-blog.csdnimg.cn/blog_migrate/3319dace6856af5d53bfd94b91200a21.jpeg)
练习:查询学号、课程号、成绩,以及显示是否及格
select 学号,课程号,成绩,
(case when 成绩>=60 then '及格'
when 成绩<=60 then'不及格'
else null
end)as 是否及格
from score;
![c3a85fb649fac4892aee67b8573a93ba.png](https://i-blog.csdnimg.cn/blog_migrate/9f23fe1ee67b551d79117a9f0b8cd73a.jpeg)
运行顺序:
![2224242d67575f4e26603c8e614f225e.png](https://i-blog.csdnimg.cn/blog_migrate/f004d8c6e472e5a45b1906a10a870484.jpeg)
![0d2d38768b77bc635639c83107c38419.png](https://i-blog.csdnimg.cn/blog_migrate/868170fc26fe59e8e44ae3d6f3f80523.jpeg)
练习:查询出每门课程及格与不及格的人数
![8d21016abcf04d9d38bfdf57eead9d0d.png](https://i-blog.csdnimg.cn/blog_migrate/83bffef072d3ee4a8102fd66665a942d.jpeg)
select 课程号,
sum(case when 成绩>=60 then 1
else 0
end)as 及格人数,
sum(case when 成绩<60 then 1
else 0
end)as 不及格人数
from score
group by 课程号;
![e2bbf4444e12a220b753a8aee2b15414.png](https://i-blog.csdnimg.cn/blog_migrate/0def893194c494b72e6edf7893334c0f.jpeg)
![81b4e6319898c44b0d85c7fa9bd5109f.png](https://i-blog.csdnimg.cn/blog_migrate/cd4483264b4b20f08ca687752498b259.jpeg)
练习:使用分段【100-85】、【85-70】、【70-60】、【<60】来统计各科成绩,分别统计:各分数段人数,课程号和课程名称
select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100
then 1 else 0 end)as '【100-85】',
sum(case when 成绩 >=70 and 成绩<85
then 1 else 0 end)as '【85-70】',
sum(case when 成绩 >=60 and 成绩<70
then 1 else 0 end)as '【70-60】',
sum(case when 成绩 <60 then 1 else 0 end)as '【<60】'
from score as a right join course as b
on a.课程号=b.课程号
-- 当用多个列来分组时,这几个列的值全部相同才算一组
group by a.课程号,b.课程名称
![23dc9dec8cdc979b0b0f220cd8af06ed.png](https://i-blog.csdnimg.cn/blog_migrate/98ec08af796bf42236bc362774bfe1eb.jpeg)
以下练习来自SQLZOO《The JOIN operation》:
The JOIN operation/zhsqlzoo.netSQLZOO练习13:Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.
SELECT mdate,team1,
sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END )as score1,
team2,
sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END )as score2
-- 这里必须用左外连接,因为可能会有一场比赛双方都没有进球的情况,使用sum运算符时如果没有记录将不会显示该条信息
FROM game left JOIN goal ON matchid = id
GROUP BY mdate, matchid, team1, team2;
![880e887555cc5de412a29746446822fb.png](https://i-blog.csdnimg.cn/blog_migrate/5a6a6f9f719bc830cb65fcd287f2bd4b.jpeg)
以下练习来自SQLZOO《More JOIN operations》:
More JOIN operations/zhsqlzoo.net![4091fa161b2298e93c9d87f567f54b89.png](https://i-blog.csdnimg.cn/blog_migrate/57278e0860219c91f71d1d9d038c1f5b.jpeg)
《SQLZOO——More JOIN operation》练习题8:顯示電影異型'Alien' 的演員清單。大于两张表查询时可以通过建立子查询来查询其他表的信息
SELECT name
FROM actor
WHERE id IN (
SELECT actorid
FROM movie as m JOIN casting as c ON m.id = c.movieid
WHERE id = (
SELECT id
FROM movie
WHERE title LIKE 'Alien'))
![f24c7ef6121632967a1848d70ff5d54e.png](https://i-blog.csdnimg.cn/blog_migrate/ff3afbafe0405c29b8bf94ed08f3da37.png)
-- 通过两个join也能实现3个表的链接
SELECT name
FROM actor as a
left join casting as c ON a.id = c.actorid
left join movie as m on c.movieid=m.id
WHERE m.title LIKE 'Alien'
练习题10:列出演員夏里遜福 'Harrison Ford' 曾演出的電影,但他不是第1主角。
select title
from movie
where id in(select movieid
from casting as c left join actor as a
on c.actorid=a.id
where name =(select name
from actor
where name='Harrison Ford')
and ord<>1)
![f903e3b6cf86f4fcf959a54338c3cc83.png](https://i-blog.csdnimg.cn/blog_migrate/563d228e690d33fb6d72bfb0cbf727f6.jpeg)
练习题11:列出1962年首影的電影及它的第1主角。
SELECT m.title, a.name
FROM casting c
JOIN movie m ON (m.id= c.movieid)
JOIN actor a ON (a.id= c.actorid)
WHERE c.ord = 1 AND yr = 1962;
![ce6f0cc11227e43ae6aa05649133b62f.png](https://i-blog.csdnimg.cn/blog_migrate/4f6165fbd1624f15458298bb2e642fea.jpeg)
练习题12:尊·特拉華達'John Travolta'最忙是哪一年? 顯示年份和該年的電影數目。
SELECT yr,COUNT(title) FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
-- from子句里的子查询需要使用括号引用,并添加as+子查询名称
(SELECT yr,COUNT(title) AS c FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
where name='John Travolta'
GROUP BY yr) AS t
)
![c1ff1c1546b6a038c11fe051fb4c077f.png](https://i-blog.csdnimg.cn/blog_migrate/13ffbaf4c3325994372fcf01baa5a39b.png)
练习题13:列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。
是否列了電影 "Little Miss Marker"兩次?
她於1980再參與此電影Little Miss Marker. 原作於1934年,她也有參與。 電影名稱不是獨一的。在子查詢中使用電影編號。
select title,name
from actor join casting on actor.id=casting.actorid
join movie on casting.movieid=movie.id
where ord='1' and movieid in(
SELECT movieid FROM casting
WHERE actorid IN (
SELECT id FROM actor
WHERE name='Julie Andrews'))
![d90d4806b2fd37805d71e053fbfa4bb9.png](https://i-blog.csdnimg.cn/blog_migrate/0bc3e4e85885b5eb692271d0c279b6fa.jpeg)
练习题14:列出按字母順序,列出哪一演員曾作30次第1主角。
SELECT DISTINCT(name) /* DISTINCT 去重 */
FROM casting c
JOIN actor a ON (a.id= c.actorid)
WHERE c.actorid IN (
SELECT actorid /* 获取30次第1主角 */
FROM casting c
WHERE ord = '1'
GROUP BY actorid /* GROUP BY + COUNT 统计次数 */
HAVING COUNT(ord) >= 30) /* HAVING 获取大于30的次数 */
ORDER BY name
![4c805d9acca15d158ca3fe000caddd08.png](https://i-blog.csdnimg.cn/blog_migrate/7ea4c434f4f0b4d6a2ee76a7089e7059.png)
练习题15:列出1978年首影的電影名稱及角色數目,按此數目由多至少排列。
方法一:
select title,count(actorid) from
movie as m join casting as c on(m.id=c.movieid)
where yr='1978'
group by title
order by count(actorid) desc,title
方法二:
select title,name_sum from
movie as m join (
select movieid,count(name) as name_sum/* 首先制作movieid和角色数目的表 */
from casting as c join actor as a on(c.actorid=a.id)
join movie as m on(c.movieid=m.id)
where yr='1978'
group by movieid)as b
on m.id=b.movieid
order by name_sum desc,title
![b985dd2ef611d0049e7ecd02af53c05d.png](https://i-blog.csdnimg.cn/blog_migrate/67d225cf658992d26adfc36b3c940d02.jpeg)
练习题16:列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名。
select name from
actor as a join casting as c on(a.id=c.actorid)
where movieid in(
select movieid
from casting as c join actor as a on(c.actorid=a.id)
where name='Art Garfunkel')
and name<>'Art Garfunkel'
![5a3cf1168f570b21adb7e44f4c4ea6d8.png](https://i-blog.csdnimg.cn/blog_migrate/b2ac38d2a3cb840b1429c8d278eb137c.png)