查询结果取交集_多表查询

最新推荐文章于 2024-04-18 19:00:05 发布
最新推荐文章于 2024-04-18 19:00:05 发布
阅读量822 收藏
点赞数
文章标签： 查询结果取交集
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39582724/article/details/112612184
版权
1.表的加法
2.表的联结
3.联结应用案例
4.case表达式

select 查询结果 -- 第五步

from 查询表名 -- 第一步

where 查询指定条件 -- 第二步

group by 分组 -- 第三步

having 对分组结果指定条件 -- 第四步

order by 查询结果排序 -- 第六步

limit 从结果中取出指定行数; -- 第七步

注意：from后面不能直接使用别名

bae98037d545ff8e56d29750a10931c2.png

准备：新建一张表

CREATE TABLE course1(课程号 VARCHAR(255), 课程名称 varchar(255) NOT NULL, 教师号 VARCHAR(255) NOT null, PRIMARY KEY(课程号));

INSERT INTO course1(课程号,课程名称,教师号) VALUES('0001','语文','0002');

INSERT INTO course1(课程号,课程名称,教师号) VALUES('0004','计算机','0004');

INSERT INTO course1(课程号,课程名称,教师号) VALUES('0005','数据库','0005');

1.表的加法

union表示求合集（去重），union all表示求合集（不去重）

select 课程号,课程名称,教师号

from course

union

select 课程号,课程名称,教师号

from course1

2d9ec73294486801b07113b714387a25.png

select 课程号,课程名称,教师号

from course

union all

select 课程号,课程名称,教师号

from course1

f5645ab16080b5ca1e3d5bd6820dabc2.png

2.表的联结

交叉联结（笛卡尔积）cross join，得到的行数是两个表行数乘积。

实际应用比较少。

内联结inner join，得到的是两个表的交集。(注意，join和inner join的效果是一样的)

select a.学号,a.姓名,b.课程号

from student as a inner join score as b

on a.学号=b.学号;

03771681d438f872fd93b65c913fb34c.png

左联结left join，左侧表中的数据全部取出，在右侧表查不到的补全为Null

SELECT a.学号,a.姓名,b.课程号,b.成绩

from student as a left join score as b

on a.学号=b.学号;

b7bcc45adb65b81b8ece3a5289ee5912.png

SELECT a.学号,a.姓名,b.课程号,b.成绩

from student as a left join score as b

on a.学号=b.学号

where b.学号 is Null;

cf9612a9a425c8d2a5cfce998d4463d5.png

默认情况下推荐使用is null 作条件判断，因为sql默认情况下对where xx =null的判断永远返回0行，不提示语法错误。

右联结right join

select b.学号,a.姓名,b.课程号

from student as a

right join score as b

on a.学号=b.学号;

a658c2b09c87cd15823c7f3daebbb164.png

利用学号配对student中的姓名等信息，学号不在student表中则补全null。

select b.学号,a.姓名,b.课程号

from student as a

right join score as b

on a.学号=b.学号

where a.学号 is null

order by 姓名;

8983841744c1898f9de9dcc61fd99f0c.png

使用is null，取学号在score表中，但不在student表的数据。

全连接full join，返回左表和右表中的所有行，有匹配的行则进行合并，没有的补全null。

MySQL不支持全连接。

3.联结应用案例

准备两张表student和score。注意两张表中学号没有包含关系，但是有交集。

2b82c5704c86045efa12d86f53a6f862.png

1818d783e848e47c71a773c2b658b4ec.png

案例：查询所有学生的学号、姓名、选课数、总成绩

思路：先将student和score左联结，然后分组计数。

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩

from student as a left join score as b

on a.学号 = b.学号

group by a.学号;

a1851b30bef49bd962e3559ba5726a66.png

实际上最好用全连接，因为student表可能比score多若干学号、同时少若干学号。

但是MySQL不支持全连接。可以先查询score表中学号不在student表的信息，然后union all。

如下是学号在score中，但不在student中的数据。（如下有两种方法）

select 学号,(Null) as 姓名, count(课程号) as 选课数, sum(成绩) as 总成绩

from score

where 学号<>all(select 学号 from student)

GROUP BY 学号;

d457a90d8a1f64664c7cc3da4c117046.png

select b.学号,a.姓名,count(b.课程号) as 选课数, SUM(b.成绩) as 总成绩

from student as a RIGHT JOIN score as b

ON a.学号=b.学号

where a.学号 is null

GROUP BY b.学号;

7fcf1562015e9304d3a4e5f1b6bf840b.png

因此最终结果如下：

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩

from student as a left join score as b

on a.学号 = b.学号

group by a.学号

union all

select 学号,(Null) as 姓名, count(课程号) as 选课数, sum(成绩) as 总成绩

from score

where 学号<>all(select 学号 from student)

GROUP BY 学号;

a96598235bdb6ac85879d9195ff0ec74.png

实际上述计算出来的总成绩是错误的。原因待后续分析。最后结果如下：

-- 内联结+左联结+右联结

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩

from student as a left join score as b

on a.学号 = b.学号

where b.学号 is null

group by a.学号

UNION ALL

select b.学号,a.姓名,count(b.课程号) as 选课数, SUM(b.成绩) as 总成绩

from student as a RIGHT JOIN score as b

ON a.学号=b.学号

where a.学号 is null

GROUP BY b.学号

UNION ALL

select a.学号,a.姓名,count(b.课程号) as 选课数, sum(b.成绩) as 总成绩

from student as a INNER JOIN score as b

on a.学号=b.学号

GROUP BY a.学号;

6eeeeef556d84b8402201b8eee16e34a.png

案例：查询个人平均成绩大于85的所有学生的学号、姓名和平均成绩。

思路：查询所有学生的学号、姓名和平均成绩，然后使用分组条件(平均成绩>85)

select a.学号,a.姓名,avg(b.成绩)as 平均成绩

from student as a right join score as b

on a.学号=b.学号

GROUP BY b.学号

HAVING avg(b.成绩)>85;

2b5b5107733be396d9f2fa98ba4c2c13.png

案例：查询学生的选课情况：学号、姓名、课程号、课程名称

select a.学号,a.姓名,b.课程号,c.课程名称

from student as a left join score as b

on a.学号=b.学号

left join course as c

on b.课程号=c.课程号

order by 学号 asc,课程号 asc;

7d8c8b66128e4c170686b53b93e3946d.png

4.case表达式（对每一行记录进行判断）

case when 判断表达式 then 表达式

when 判断表达式 then 表达式

……

else 表达式

end

注意：else可以省略，默认为null，建议不省略。end不能省略。case表达式可以写在任意子句。

示例：

select 学号,课程号,成绩,

(case when 成绩>=80 then '及格'

when 成绩<80 then '不及格'

else null

end) as 是否及格

from score;

2604dfef9c778c8ad9e217456e1b995c.png

案例：算出每门课程的及格人数和不及格人数。

思路，首先要按照课程号分组，分组后及格人数（及格设置为1，不及格设置为0），不及格人数（及格设置为0，不及格设置为1），最后对每一组求和。

select 课程号,

sum(

case when 成绩>=80 then 1

else 0

end)as 及格人数,

sum(

case when 成绩<80 then 1

else 0

end) as 不及格人数

from score

group by 课程号;

e3aeb9441dea91e38f4ad8c972eead67.png

案例：求每门课程的最高分、学号、课程号

解法一：

SELECT a.学号,a.课程号,a.成绩

from score as a,

(select 课程号,min(成绩) as 最低成绩 from score group by 课程号) as b

WHERE a.课程号=b.课程号 and a.成绩=b.最低成绩;

2c0527afa4492c48f2ec61dfc3d5a32e.png

解法二：

select * from score a

where 成绩=(select min(成绩) from score as b where a.课程号=b;课程号);

0f439097b9e838e3b70c5f3521cae93b.png

案例：查询各科成绩前两名/后两名的记录。（top N的问题）

思路：这个问题没有理解，暂且记下来，后续再进行研究。

前两名：

select * from score as a

where (select count(*) from score as b where a.课程号=b.课程号 and a.成绩<b.成绩)<2

order by a.课程号,a.成绩 desc;

f5b4c95f882eabb6537421d8f8c8c2b2.png

后两名：

select * from score as a

where (select count(*) from score as b where a.课程号=b.课程号 and a.成绩>b.成绩)<2

order by a.课程号,a.成绩 desc;

290fb92e3b31e35a02e0552b4d596584.png

另外还有一种比较粗暴的方法：

先查出课程号（即有哪些分组），然后对每个分组的成绩排序，使用limit取topN，最后使用union all进行合并。

select 课程号 from score group by 课程号;

1b4ec5a02b013612b46494be57b311c5.png

select 学号,课程号,成绩 from score

where 课程号='0001'

order by 成绩 desc

limit 2

a39cd1c606484364b65fb7035f4b55bc.png

因此，可以将三门课程的top 2合并起来：

c17924d6c1418f3ff7f817e2a30ae173.png

案例：分段统计各科成绩，并对应课程名称

使用分段[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 成绩 between 70 and 85 then 1

else 0

end

) as '[85-70]',

sum(

case when 成绩 between 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 b.课程号

045d162eba1e7f8e2e1902ed6e40ad83.png
weixin_39582724
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL 查询结果取交集的实现方法
09-11
本文将详细介绍MySQL中如何实现以SQL查询返回的结果集取交集的实现方法，需要的朋友可以参考
oracle 合并查询 事务 sql函数小知识学习
09-10
3. `INTERSECT`：返回两个或更多SELECT语句的交集，即同时存在于所有查询结果中的行。 ```sql SELECT ename, sal, job FROM emp WHERE sal > 2500 INTERSECT SELECT ename, sal, job FROM emp WHERE job = '...
mybatis-plus AND条件模糊查询 取交集
sxudong2010的博客
12-02 1261
@Override public PageUtils queryPage(CarInfoDTO carInfoDTO) { Map<String, Object> params = new HashMap<>(); params.put("page", carInfoDTO.getPageNumber()); ...
case when 子查询_SQL之多表查询篇
weixin_39833270的博客
11-22 1724
一、表的加法/组合查询1.unionunion将两个表的数据按行合并在一起，两个表重复的数据只保留一个。coursecourse1合并：2.union allunion all将两个表的数据按行合并在一起并保留重复行。注意：作为集合运算对象的表的前提是列数要一致。运算结果会导致行的增减，但是不会导致列的增减。二、表的联结联结的分类：1.交叉联结cross join交叉联结又称笛卡尔积，交叉联结是对...
MySQL查询并集、交集、差集的方式是什么
最新发布
DN金猿的博客
04-18 872
需要注意的是，以上查询方式都是基于表之间的列相等条件，可以根据具体需求进行适当的调整。另外，还可以使用其他方式如子查询、临时表等来实现类似的功能。
Oracle中的交差并集
m0_71406734的博客
05-06 2184
Oracle中的交差并集合
MySQL常见的求交集、并集和差集
mnimxq的博客
09-15 862
1、select a.*,b.* from tableA a left join tableB b on a.key = b.key; 2、select a.*,b.* from tableA a right join tableB b on a.key = b.key; 3、select a.*,b.* from tableA a inner join tableB b on a.key = b.key; 4、select a.*,b.* from tableA a...
15min快速掌握Mybatis 多表操作 & 查询过程 & 查询结果
qq_51033936的博客
11-09 896
15分钟快速掌握Mybatis中的多表操作(一对一,一对多,多对多), 查询过程, 查询结果.以及学习和关键属性的应用👍👍👍
sql 语句联合查询交集
10-16
可以用来查询从两个不同的书就表中查询出结果 查询时的拼接语句
T-SQL高级查询
04-26
将一个table的查询结果当做一个新表进行查询 select * from ( select id, name from student where sex = 1 ) t where t.id > 2; 上面括号中的语句，就是子查询语句（内部查询）。在外面的是外部查询，其中外部查询...
Mybatis多表联查的几种办法
热门推荐
weixin_51667449的博客
05-20 2万+
开发过程中，对数据库多表连接查询是常规操作。Mybatis（plus）在多表连接查询上提供了很好的支持。通常，表与表之间存在四种映射关系：一对一映射、多对一映射、一对多映射、多对多映射。在处理的过程中，多对一和一对一映射一般采用同一种操作方式，一对多和多对多映射一般采用同一种操作方式。而且，对于Mybatis的运用，我们通常使用xml的方式。因此，本文主要介绍xml方式下一对一多表联查和一对多多表联查。
超详细多表查询详解-多表关系-多表查询-子查询
无限进步
03-03 1457
内连接的语法分为两种: 隐式内连接、显式内连接
【Mysql】取两个查询语句结果的交集
自律即是最自由
10-11 1782
取两个查询语句的交集操作
mysql的交集关键字,mysql查询，left join（求并集），where（求交集）
weixin_42498766的博客
03-18 379
mysql查询，left join(求并集)，where(求交集)发布时间：2020-07-05 20:08:35来源：51CTO阅读：2529作者：Lee_吉准备(两张表t1,t2)：表t1:mysql> select * from t1;+-------+---------+| t1_id | t1_name |+-------+---------+| 1 | t1_1 |...
flink1.7.2 tableapi批处理示例
测试0901-1
03-16 307
flink1.7.2 tableapi批处理示例 源码 https://github.com/opensourceteams/flink-maven-scala 概述 本文为flink1.7.2 tableapi批处理示例 主要操作包括: print table,DataSet 转换成table,Scan,select,as,where / filt...
select查询（交、并、差、联表查询、子查询）
赟文武的博客
06-25 903
学习心得
MySQL实现交集查询和差集查询以及对称差查询
ChaoYue_miku的博客
11-26 6626
集合查询
查询结果取交集_SQL学习笔记（五）多表查询
weixin_35343226的博客
12-27 458
从零学会SQL：多表查询​www.zhihu.com本篇将结合课程内容和《SQL基础教程》中的第7章内容对集合运算进行整理。之前的学习多为在一张表上的操作，本篇主要通过以行方向（竖）为单位的集合运算符和以列方向（横）为单位的联结，将多张表中的数据进行组合。什么是集合运算？集合运算就是对满足同一规则的记录进行的加减法的四则运算。通过集合运算，可以得到几张表中记录的集合或者公共记录的集合，又或者其中某...
sql求交集与差集
bitcarmanlee的博客
12-09 1万+
日常工作中，针对两个表A，B，求A与B表中同一个字段的交集与差集是常见需求，下面我们来总结一下求交集与差集的方法。假设现在有两张表A,B，A,B表中均有一个字段为id，现在我们想求A与B中都存在的id有多少个(去重)，在A中但不在B中的id有多少个。
json取交集_查找MySQL JSON对象或数组的交集
05-17
要查找 MySQL JSON 对象或数组的交集，可以使用 MySQL 内置的 JSON 函数。 假设我们有两个 JSON 数组： ...这个查询会返回结果： ```json ["banana", "pear"] ``` 说明两个 JSON 数组的交集是 "banana" 和 "pear"。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值