case when 子查询_SQL多表查询

fa03419d9b4fc36a424e0f8841f36b32.png

1. 表的加法 (union)

快速建立相同结构表(以course为例):

course ---》右键单击---》复制表---》结构和数据---》对新表重命名---》改数据

048e6dcbaf2a8e093ed489845957ba0c.png
新建course1表

1)union不保留重复行

845a6f47654ee65fc09e594dd139b6bc.png
union all SQL语句示例

2)union all 保留重复行

e4530b89bd7136ecda26e568020503ca.png
union all SQL语句示例

2. 表的联接

816ba01c1ca44085b27cb2760fffd694.png

1) 交叉联接(cross join):实际业务中应用比较少,但是其他联接的基础

0d42a3ac7ee7bb9ccd56f874ddb3193b.png
交叉联接

2) 内联接(inner join):交集

取数图例:

942a2cc05c3bc793c97c62f114fada38.png
内联接图例1

c500e144de1f116d7ce66148c82ad6c5.png
内联接图例2

SQL语句:

select a.学号, a.姓名, b.课程号
from student as a inner join score as b
on a.学号 = b.学号;

f2000539459f65b4fa83be9586b62111.png
内联接语句查询

3) 左联接(left join)

取数图例:

2b46072e50a58a234fa1e7fe8f24e459.png
左联接图例1

4b095944d2477195f9411d64cef6132c.png
左联接图例2

SQL语句:

select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号 = b.学号;

df1dfd50308487ffec8535b93c563a9b.png
左联接语句查询

SQL语句:去掉交集部分

select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号 = b.学号
where b.学号 is Null;

82bc8a586532b5c27c31d0693061f296.png
左联接筛选语句去交集

4) 右联接(right join)

取数图例:

304ceb3eb9422589797b2a8f1c8bc0f8.png
图例1

44dd7d30348c23ecbe076c69125cb4e0.png
图例2

SQL语句:

select a.学号, a.姓名, b.课程号
from student as a right join score as b
on a.学号 = b.学号;

f668262fdef350dbe69ee73683f11bac.png
右联接语句查询

SQL语句:去掉交集部分

select a.学号, a.姓名, b.课程号
from student as a right join score as b
on a.学号 = b.学号
where a.学号 is Null;

9386912520f7fe9b097980f36a2b8942.png

5) 全联接(full join):MySQL不支持全连接

取数图例:

8d184e35d74e9c5abfe53500287885e6.png
图例1

ed8d77d36ae8d6b2dafe916201c816f2.png
图例2

联接总结图

54cb288a7cc7f2fea99e5b5aa3ce5728.png

3. 联接应用案例

问题1:查询所有学生的学号、姓名、选课数、总成绩

分析:
1)学号、姓名(student表)
2)选课数(每个学生的选课数、score表)
3)总成绩(每个学生的总成绩,score表,按学号分组,对成绩求和)

见图:

ec07ef663da266f08cc8ca92ef06f5ca.png

SQL语句

select a.学号, a.姓名, count(b.课程号) as 选课数, sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;

e443363284c5b9b6e159de98720e93bd.png
SQL查询结果

问题2:查询平均成绩大于85的所有学生的学号、姓名、平均成绩

分析:
1) 查询出所有学生的学号、姓名、平均成绩、学号、姓名(student表)
平均成绩(score表),按学号分组求平均成绩
2) 平均成绩 > 85

SQL语句

select a.学号, a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
where avg(b.成绩) > 85
group by a.学号
having avg(b.成绩) > 85;

b98074f93066ceb905efbb4e72b77683.png
SQL查询结果

问题3:查询学生的选课情况:学号、姓名、课程号、课程名称

分析:
1) 学号、姓名(student表)
    课程号(score表)
2) 课程名称(course表)

SQL语句

select a.学号, a.姓名, b.课程号, c.课程名称
from student as a left inner join score as b
on a.学号 = b.学号
left inner join course as c
on b.课程号 = c.课程号;

7cfe716ed5ceebea84a00a59265b527f.png
SQL语句查询结果

4. case 表达式

case when (判断表达式) then (表达式)
     when (判断表达式) then (表达式)
     when (判断表达式) then (表达式)
     …
     else(表达式)
end

例1:

select 学号,课程号,成绩,
(case when 成绩 >= 60 then '及格'
      when 成绩 < 60 then '不及格'
      else
end) as 是否及格
from score;

43d48d7907681f783325b76ccb5a07d0.png
SQL语句查询

问题1:查询每门课程的及格人数和不及格人数

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 课程号;

注:sum()不应写成sum ()

步骤见图:

d3fe2051aae1046cf48f496672eef203.png

b44aba8cb55454baae372d8858319f7d.png
SQL语句查询成绩优秀或良好人数
注意事项:
1. else 子句可以默认不写,但是为了完整性,保留
2. end 不可省略不写
3. 该表达式可写在任何子句中
 

问题2:使用分段[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.课程名称; (这里加上课程名称对结果无影响)

6e1073aab2bbc7b9603b613166443cd6.png
SQL语句查询成绩段

SQLZOO练习

1.列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員,要檢查: teamid = 'GER'

select matchid, player
from goal 
where teamid = 'GER';

8ebf779a490272aafa276d2c51ef6d69.png

2. 在表格 game中找出賽事1012的資料。只顯示賽事1012的 id, stadium, team1, team2

select game.id, game.stadium, game.team1, game.team2
from game left join goal
on game.id = goal.matchid
where game.id = '1012' and goal.matchid = '1012'
and goal.player = 'Lars Bender';

6c898cbd4db735568ee398a2585b1edb.png

3.顯示每一個德國入球的球員名,隊伍名,場館和日期。

select goal.player, goal.teamid, game.stadium, game.mdate
from game inner join goal  (之前用left join,但此处为两表共有的部分)
on game.id = goal.matchid
where goal.teamid = 'Gre';

7e6dc621c6ff77ded4546e198b36149b.png

以上结果与答案不符,可能数据有改变。

4. 列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player

select g1.team1, g1.team2, g2.player
from game as g1 right join goal as g2
on g1.id = g2.matchid
where g2.player like 'Mario%';

名字的模糊查询。

03383da627cc33e3689f51400950cdee.png

5. 列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime

select g.player,g.teamid,e.coach,g.gtime
from goal as g left join eteam as e
on g.teamid = e.id
where g.gtime <= 10;

指定on的联接条件不能忘写,答案显示为错误,但是其给出的答案只是三次行改变了相对位置。

e85a8b3c58064099f8ff1d26faa60bad.png

6. 列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期,和隊伍名。

select g.mdate, e.teamname
from game as g left join eteam as e
on e.id = g.team1
where e.coach = 'Fernando Santos';

2d005f90f4184626b312094da1a675ff.png

7. 列出場館 'National Stadium, Warsaw'的入球球員。

select g2.player
from game as g1 left join goal as g2
on g1.id = g2.matchid
where g1.stadium = 'National Stadium, Warsaw';

affbf0dd0d856b85cabb1ade1e8d79ee.png

8. 以下例子找出德國-希臘Germany-Greece 的八強賽事的入球

修改它,只列出全部賽事,射入德國龍門的球員名字。

select player
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where (g1.team1 = 'Ger' or g1.team2 = 'Ger')
and g2.player not in (select player
                      from goal
                      where teamid = 'Ger');

和给出的答案有出入,原因在于选取的名字应该用distinct

3998a794a908c7c2d62ea53a80f657d0.png
select distinct b.player
from game as a inner join goal as b on a.id = b.matchid
where (b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER');

以上语句可去重。

9.列出隊伍名稱teamname和該隊入球總數

select e.teamname, count(g.teamid)
from goal as g inner join eteam as e
on g.teamid = e.id
group by e.teamname;

0c376724513adf08bbe21ec58189d2a1.png

如果不用group by 分组,那么球队和进球数均为总数。

10. 列出場館名和在該場館的入球數字。

select g1.stadium, count(g2.matchid)
from game as g1 inner join goal as g2
on g1.id = g2.matchid
group by g1.stadium;

19b0b7e62108ac55d18f88698faa702e.png

11. 每一場波蘭'POL'有參與的賽事中,列出賽事編號 matchid, 日期date 和入球數字。

select g2.matchid, g1.mdate, count(g2.matchid)
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where g1.team1 = 'POL' or g1.team2 = 'POL'
group by g2.matchid;

1c8a95477c0748a742fb7334eb82b359.png

12. 每一場德國'GER'有參與的賽事中,列出賽事編號 matchid, 日期date 和德國的入球數字。

select g2.matchid, g1.mdate, count(g2.teamid = 'GER')
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where (g1.team1 = 'GER' or g1.team2 = 'GER')
and g2.teamid = 'GER'
group by g2.matchid;

c968249d5d9b359cc1bcb5452158a222.png

13. 每场比赛主客队进球数。

select g1.mdate, g1.team1,
sum(case when g1.team1 = g2.teamid then 1
          else 0
end ) as score1,
g1.team2,
sum(case when g1.team2 = g2.teamid then 1
          else 0
end ) as score2
from game as g1 left join goal as g2
on g1.id = g2.matchid
group by g1.mdate,g1.id,g1.team1,g1.team2
order by g1.mdate,g1.id,g1.team1,g1.team2;

486b29dc6231cb0a31632b8dc9294a60.png

此处自己问题在于,group by和order by 后之前没有添加g1.id,g1.team1,g1.team2;添加多个分组可避免同一天有多个比赛时,不知会只有一场比赛。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值