mysql SQL 集锦：join_and 和 多次join同一张表的处理 和 group_concat 和 find_in_set

已于 2024-09-20 19:44:39 修改
阅读量2.1k 收藏 8
点赞数 1
分类专栏： 数据库 文章标签： mysql 数据库
于 2022-11-02 18:16:30 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cpxsxn/article/details/127656396
版权

Join 和 and

在使用left join时，on and和on where条件的区别如下：
1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉，on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。

表结构
	SQL> create table A (id int, type int);
	SQL> select * from A;
	        ID       TYPE
	---------- ----------
	         1          1
	         2          1
	         3          2
	 
	SQL> create table B(id int ,class int);
	SQL> select * from B;
	 
	        ID      CLASS
	---------- ----------
	         1          1
	         2          2

不使用条件
	SQL> select * from A left join B on A.id = b.id;
	 
	        ID       TYPE         ID      CLASS
	---------- ---------- ---------- ----------
	         1          1          1          1
	         2          1          2          2
	         3          2
	

使用条件where
	SQL> select * from A left join B on A.id = B.id where A.type = 1;
	 
	        ID       TYPE         ID      CLASS
	---------- ---------- ---------- ----------
	         1          1          1          1
	         2          1          2          2
	根据上面那段话的解释，where字句是在生成临时表以后再进行过滤的，也就是可以理解为就是一个左连接：
	select * from A left join B on A.id = B.id，然后加上where A.type = 1对临时表进行过滤，除掉A.type不为1的数据。

 

使用and 
	SQL> select * from A left join B on A.id = B.id and A.type = 1;  （这需要操作确认）
	 
	        ID       TYPE         ID      CLASS
	---------- ---------- ---------- ----------
	         1          1          1          1
	         2          1          2          2
	         3          2
	因为左连接不管on and语句是否为真都必须返回左表所有的记录，所以and A.type=1;没有起到任何作用。

	SQL> select * from A left join B on A.id = B.id and B.class = 1;
	 
	        ID       TYPE         ID      CLASS
	---------- ---------- ---------- ----------
	         1          1          1          1
	         3          2
	         2          1	
	根据上面那段话的解释：on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
	显然左连接再加上新的条件:B.class = 1筛选掉第二行记录，结果正确。

使用 where：
	SQL> select * from A left join B on A.id = B.id where B.class = 1;
	 
	        ID       TYPE         ID      CLASS
	---------- ---------- ---------- ----------
	         1          1          1          1

	where是生成临时表以后再进行过滤，对左右表都进行筛选。
	如果是and，先不管过滤规则，先把左边表数据全部返回，然户过滤规则只对右表产生过滤。

再来看看内连接inner join  on and和 on where的区别：
在使用inner join时，不管是对左表还是右表进行筛选，on and和on where都会对生成的临时表左右两边同时进行过滤。

表结构 
	SQL> select * from A;
	 
	        ID       TYPE
	---------- ----------
	         1          2
	         2          1
	         3          2
	 
	SQL> select * from B;
	 
	        ID      CLASS
	---------- ----------
	         1          1
	         2          2

inner join 的特性就是只返回有匹配的
	SQL> select * from A inner join B on A.id = B.id;
	 
	        ID       TYPE         ID      CLASS
	---------- ---------- ---------- ----------
	         1          2          1          1
	         2          1          2          2
	SQL>
	SQL> select * from A inner join B on A.id = B.id where A.type = 1;
	 
	        ID       TYPE         ID      CLASS
	---------- ---------- ---------- ----------
	         2          1          2          2
	SQL> 
	SQL> select * from A inner join B on A.id = B.id and B.class = 1;
	 
	        ID       TYPE         ID      CLASS
	---------- ---------- ---------- ----------
	         1          2          1          1
	SQL>
	SQL> select * from A inner join B on A.id = B.id where B.class = 1;
	 
	        ID       TYPE         ID      CLASS
	---------- ---------- ---------- ----------
	         1          2          1          1
	
	发现会对左右两边同时过滤。

多次join同一张表的处理: join and or

select * from (
select nod.*, concat_ws(",",vi.vip, vi2.vip) as vip, vi.relation_id as aaa from node nod
left join cluster clu on clu.name = nod.cluster_name  and clu.owner = "sulijun6"
left join virtual_ip vi on nod.uniq_id = vi.relation_id and nod.role in ("master", "slave")
left join virtual_ip vi2 on nod.group_id = vi2.relation_id and nod.role = "roleless"
) as aa



select my_db.db_name, my_db.description, 
node.ip as ip, node.port as port, node.`role` as role, 
app.app_name_en, app.app_name_cn, app.owner, 
group_concat(distinct(account.hosts)) as app_hosts
from my_db
left join app on app.cluster_name = my_db.cluster_name 
left join account on account.cluster_uniq_id = my_db.cluster_uniq_id 
left join ha_group hg on hg.cluster_uniq_id = my_db.cluster_uniq_id 
left join proxy_group pg on pg.cluster_uniq_id = my_db.cluster_uniq_id 
left join node on (node.group_id = hg.uniq_id and node.`role` in ('master', 'slave')) or (node.group_id = pg.uniq_id and node.`role` = 'roleless')
group by app.app_name_en, my_db.db_name, node.ip

group_concat: 没有 group by 也可以使用

mysql> select * from student;
+----+----------+--------+---------+----------------------------+----------------------------+------------+
| id | name     | gender | address | create_date                | update_date                | country_id |
+----+----------+--------+---------+----------------------------+----------------------------+------------+
|  2 | fromjdbc |      2 | HeNan   | 2020-12-25 15:13:18.000000 | 2020-12-25 15:13:18.000000 |          1 |
|  3 | Michael  |      1 | BeiJing | 2020-12-25 15:13:55.000000 | 2020-12-25 17:07:56.024261 |          2 |
|  4 | cpx      |      1 | BeiJing | 2020-12-30 19:46:41.000000 | 2020-12-30 19:46:41.000000 |          2 |
|  5 | cpxx     |      1 | BeiJing | 2020-12-30 19:46:50.000000 | 2022-06-07 10:30:33.432000 |          1 |
|  6 | aa       |      2 | aaa     | 2022-10-26 19:06:26.000000 | 2022-10-26 19:06:26.000000 |          1 |
|  8 | jdbc1    |      1 | SH      | 2022-10-26 08:18:29.438000 | 2022-10-26 08:18:29.438000 |          1 |
|  9 | 11111    |      1 | SH      | 2022-10-26 08:21:22.657000 | 2022-10-26 08:21:23.255000 |          1 |
| 10 | jdbc1    |      1 | SH      | 2022-10-26 08:23:51.336000 | 2022-10-26 08:23:51.860000 |          1 |
| 11 | jdbc0    |      1 | SH      | 2022-10-26 08:25:48.981000 | 2022-10-26 08:25:48.981000 |          1 |
| 12 | jdbc1    |      1 | SH      | 2022-10-26 08:25:48.981000 | 2022-10-26 08:25:48.981000 |          1 |
+----+----------+--------+---------+----------------------------+----------------------------+------------+
10 rows in set (0.00 sec)

mysql>
mysql> select group_concat(name  SEPARATOR ',') from student;
+------------------------------------------------------------+
| group_concat(name  SEPARATOR ',')                          |
+------------------------------------------------------------+
| fromjdbc,Michael,cpx,cpxx,aa,jdbc1,11111,jdbc1,jdbc0,jdbc1 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

find_in_set

FIND_IN_SET(str,strlist)，该函数的作用是查询字段(strlist) 中是否包含(str)的结果，返回结果为 null或记录 。

SELECT FIND_IN_SET('b', 'a,b,c,d');
// 结果：2.  因为 b 在strlist集合中2的位置, a是位置1
robch
关注
专栏目录
【MySQL知识点】MySQL中 left join 的on和where的差异
风暴计划
11-20 788
目录 1 建表 2 插入数据 3 测试 3.1左连接: A left join B on A.id=B.id, 返回记录数与A表记录数一致,无论A/B表是否能匹配上 3.2 左连接中, on条件与where条件过滤的差异 3.3使用 where... is null子句的 left join 【主旨：MySQL中 left join 的on和where的差异】 1 建表 drop table ASM_Product if exists; create table ASM_Pr...
mysql join
weixin_34337381的博客
06-04 107
2018-6-4 10:28:50 星期一 开发中一直在用 left join, 心中只有一丝丝的了解, 还都是学校里学的, 今天看了几遍文章这里记录一下 sql的left join 、right join 、inner join之间的区别 　　left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 　　right join(右联接) 返回包括右表中的所有记录和左表...
MySQL 多表连接（JOIN）
最新发布
yimeixiaolangzai的博客
08-19 2061
在关系型数据库中，数据通常会被存储在不同的表中。为了从这些分散的表中获取有用的信息，我们需要使用 JOIN 操作来将这些表连接在一起。JOIN 操作的本质是通过某种条件将多张表的数据结合在一起，形成一个新的结果集。MySQL 的多表连接是数据库查询中不可或缺的工具，通过 JOIN 操作，我们可以轻松地在多个表中提取、组合数据。熟练掌握不同类型的 JOIN 并理解其适用场景，可以帮助我们在复杂的业务需求中快速构建高效的查询。
mysql多次join同一张表的处理
lvshaorong的博客
11-17 3万+
在做多表联查的时候有时候会碰到一条sql需要多次查同一张表的情况，比较常见的是主表和码值表的匹配。如主表的多个列都记录了编号信息而非实际内容，需要去码值表去查对应的实际内容，就需要多次join码值表。 举个简单的例子，一个班有两个老师，在班的表里记录了两个老师的id，现在要把id换成这两个老师的名字，就需要按照老师的id去查老师表，也就是班级表需要两次join老师表来获取两个老师的名字，如果直接...
SQL高级查询技巧（两次JOIN同一个表，自包含JOIN，不等JOIN）
weixin_34291004的博客
11-17 2846
掌握了这些，就比较高级啦 Using the Same Table Twice 如下面查询中的branch字段 SELECT a.account_id, e.emp_id, b_a.name open_branch, b_e.name emp_branch FROM account AS a INNER JOIN branch AS b_a ON a.open_branch_id = b...
mysql join的on条件内 可以带and建立多条件join
热门推荐
无效的博客
08-19 6万+
mysql join on 可以带and，例如： select * from (select id,realname from `user` where id=157) a left join `crm_customer2user` cu on a.id=cu.userId a
MySQL GROUP_CONCAT限制解决方案
12-14
　GROUP_CONCAT函数可以拼接某个字段值成字符串，默认的分隔符是 逗号，即”,” ， 　如果需要自定义分隔符可以使用 SEPARATOR 　如： SELECT GROUP_CONCAT(name SEPARATOR '_') FROM user 限制： 　GROUP_CONCAT...
mysql中in与like效率比较_mysql 中LIKE 与FIND_IN_SET 与关联表left join 速度效率比较
weixin_32880357的博客
01-26 2058
有一张表Table有IDStr字段，如下只显示二个字段还有很多其他字段方式一 字段逗号分割，直接用UserIDStr字段，里面存多个ID用逗号分割UUID　　UserIDStr1111　　1,2,32222　　3方式二用关联表，关联表如下：UUID　　UserID1111　　 11111　　 21111　　 3222　　　3测试了50W条数量-- 方式一设计用 Like查几次时间分别是： 1.19...
mysql：group_concat对空值的处理
dreamyflorasky的博客
01-30 1978
group_concat对空值的处理
MySQL 8.0.23上遇到一个FIND_IN_SET的BUG
老叶茶馆
10-20 1224
本文为网友投稿。作者：蓝树叶（成伟），运维工程师。1、背景在MySQL 8.0.23出现执行SQL超时的报错，而在MySQL8.0.18版本上未出现，故做个记录，以飨读者。2、故障提要在客...
mysql的SUBSTRING_INDEX 和GROUP_CONCAT
念兮为美
08-17 4760
今天在做项目中的一个小模块，就是查询所有项目的应收款。当我拿到这个模块时，并不知道该怎么做？因为刚毕业，没有做过这样的项目，但又不得不做。幸好出生在这个网络信息纷飞的年代，于是通过谷歌来查询解决方法，最终得出了自己的方法。所以，当我们拿到自己没有做过的项目，千万不要说不会做，而是通过工具得出解决方案。 应收款放在变更表中，关联到项目财务科目的id。项目财务表存储的对应的是一期、二期、三期等应收款......
mysql 多表连接on 后加and
weixin_31759829的博客
07-21 97
MySQL 多表连接使用 ON 后加 AND 的应用 在数据库查询中，多表连接是一种常见的操作，用于从多个表中获取数据。MySQL 支持多种连接类型，如内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）等。在进行多表连接时，我们通常使用 ON 子句来指定连接条件。有时，我们需要在 ...
Mysql关于join on where(and)的区别
macallbobo的专栏
04-22 1163
Macall...
【SQL】 关于join on and 查询
静墨客的博客
08-04 470
关于join on and 查询
SQL进阶(十四)使用两次join实例
北冥的博客
12-18 3813
题目： 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工 CREATE TABLE departments ( dept_no char(4) NOT NULL, dept_name varchar(40) NOT NULL, PRIMARY KEY (dept_no)); CREATE TABLE dept_emp ( emp_no...
MySQL的join你真的了解吗！！！
qq_48157004的博客
11-27 2810
1. 应该使用小表作为驱动表2. 被驱动表关键字段应该建立索引3.被驱动表无法使用索引、数据量很大时，因适当调整join_buffer大小。避免 Buffer Pool 的热数据被淘汰，影响内存命中率。4.MySQL8.0之后版本，已经支持hash join，没有升级的小伙伴赶紧升级把!!!阿根廷加油，梅老板牛逼！！！
MySQL--内连接查询(inner join）
gezongbo的博客
10-15 2万+
1）内连接查询 – inner join 语法1：select f1,f2,f3... from table1 inner join table2 on 关联匹配条件 语法2: select f1,f2,f3... from table1,table2,table3 where 关联匹配条件 （1）自连接 概念：是内连接查询中一种特殊的等值连接，所谓的自连接就是指表与其自己当前表进行连接。自己和自己做连接。 例子：查询员工表，
SQL 中JOIN ON 的AND条件设置
tjbsl的博客
09-05 5883
关于在A LEFT JOIN B ON A.id =B.id 后面跟AND 条件的测试。 有如下的结构数据： 注意此LEFT ON 跟AND 限制A表的场景； dat a.sql 1.43KB 有如下的表结构 使用SQL进行查询： （1）SELECT * FROM `table_name` a LEFT JOIN table_age b ON a.id = b.id; （2）SELECT * FROM `table_name` a LEFT JOIN tab...
GROUP_CONCAT和FIND_IN_SET联合使用
07-27
GROUP_CONCAT 和 FIND_IN_SET 可以在 MySQL 中联合使用来实现一些特定的查询需求。 GROUP_CONCAT 函数用于将多行数据按照指定的分隔符连接成一个字符串。它可以与 GROUP BY 子句一起使用，将分组后的结果进行字符串拼接。 FIND_IN_SET 函数用于在一个逗号分隔的字符串列表中查找特定值，并返回其在列表中的位置。它接受两个参数，第一个参数是要查找的值，第二个参数是逗号分隔的字符串列表。 下面是一个示例，演示如何使用 GROUP_CONCAT 和 FIND_IN_SET 联合使用： ```sql SELECT group_id, GROUP_CONCAT(user_name) AS users FROM user_groups WHERE FIND_IN_SET('John', user_list) > 0 GROUP BY group_id; ``` 在上面的示例中，我们有一个 user_groups 表，其中包含 group_id 和 user_list 字段。我们想要查找包含特定用户（例如 'John'）的组，并将该组中的所有用户名连接成一个字符串。 使用 FIND_IN_SET('John', user_list) > 0 条件可以筛选出包含 'John' 的组。然后，通过 GROUP_CONCAT(user_name) 将这些组中的用户名称连接成一个字符串，并使用 GROUP BY group_id 进行分组。 注意：GROUP_CONCAT 默认情况下有长度限制，如果连接后的字符串超过了最大长度限制，可以通过设置 group_concat_max_len 参数来调整。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值