MySQL 合并查询 join 查询出的不同列合并到一个表中

已于 2023-07-27 16:33:24 修改
阅读量4.3k 收藏 11
点赞数 3
分类专栏： MySQL 文章标签： mysql 数据库
于 2023-02-01 12:15:14 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chengyj0505/article/details/128773098
版权

为了求解问题时思路清晰，建议先分列查询，再将列合并到一个表中，这样相当于将复杂问题拆解为简单问题一一解决。优点是避免所有问题混在一起，代码逻辑清晰，可迁移性强，下次遇到类似的查询问题能快速求解，缺点是代码看起来不够简洁，存在代码冗余的问题。

一、适用场景和方法

（1）适用场景

考虑查询过程中是否存在以下情况：

  • 查询某些列时需要分组才能得到，某些列不需要分组就能得到；
  • 查询某些列时需要where条件，某些列不需要where条件；
  • 查询这些列时需要多次用到不同的表；
  • 某一个列或几个列的查询过程很复杂。

存在上述情况时候，为了求解问题时思路清晰，建议先分列查询，再将列合并到一个表中，这样相当于将复杂问题拆解为简单问题，一一解决。

（2）方法

MySQL多表查询，将查询到的列合并到一个表中使用join函数

具体包括：

连接类型（四者选一）连接条件（三者选一）
left joinnatural
right joinon <连接条件>
inner joinusing(col1,col2,…,coln)
full outer join

根据查询需要使用不同的连接类型和条件。其中col指列名（注意两个表的该列名必须相同）。

二、案例分析

案例来自：SQL135 每个6/7级用户活跃情况

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

iduidnick_nameachievementleveljobregister_time
11001牛客1号31007算法2020-01-01 10:00:00
21002牛客2号23007算法2020-01-01 10:00:00
31003牛客3号25007算法2020-01-01 10:00:00
41004牛客4号12005算法2020-01-01 10:00:00
51005牛客5号16006C++2020-01-01 10:00:00
61006牛客6号26007C++2020-01-01 10:00:00

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002C++easy602021-09-01 06:00:00
39003算法medium802021-09-01 10:00:00

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

uidexam_idstart_timesubmit_timescore
100190012021-09-01 09:01:012021-09-01 09:31:0078
100190012021-09-01 09:01:012021-09-01 09:31:0081
100590012021-09-01 19:01:012021-09-01 19:30:0185
100590022021-09-01 12:01:012021-09-01 12:31:0285
100690032021-09-07 10:01:012021-09-07 10:21:5984
100690012021-09-07 10:01:012021-09-07 10:21:0181
100290012020-09-01 13:01:012020-09-01 13:41:0181
100590012021-09-01 14:01:01(NULL)(NULL)

题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

uidquestion_idsubmit_timescore
100180012021-08-02 11:41:0160
100480012021-08-02 19:38:0170
100480022021-08-02 19:48:0190
100180022021-08-02 19:38:0170
100480022021-08-02 19:48:0190
100680022021-08-04 19:58:0194
100680032021-08-03 19:38:0170
100680032021-08-02 19:48:0190
100680032020-08-01 19:38:0180

请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。由示例数据结果输出如下：

uidact_month_totalact_days_2021act_days_2021_examact_days_2021_question
10063413
10012211
10051110
10021000
10030000

解释：6/7级用户共有5个，其中1006在202109、202108、202008共3个月活跃过，2021年活跃的日期有20210907、20210804、20210803、20210802共4天，2021年在试卷作答区20210907活跃1天，在题目练习区活跃了3天。

分析是否适用‘列拼接成表’的方法：

根据题目要求可知，总活跃月份数、2021年活跃天数和 2021年试卷作答活跃天数、2021年答题活跃天数，查询所用的表不一样，并且每一个列的查询过程都很复杂，所以采取分列查询再合并列的方法。

分析思路

难点：

1.建立合并列的思想

（1）统计用户总活跃月份数 如果日期重复算一个月份

​ [使用]：[年月]：date_format(exrp,'%y%m') ; 去重：distinct

（2）统计用户2021年活跃天数 如果日期重复算一天

​ [使用]：[2021年]: year(exrp) = 2021; [年月日]：date(exrp) ; 去重：distinct；

注意： 判断是否是2021年应该放在select里面而不是where中

（3）统计2021年试卷作答活跃天数

​ [使用]： [2021年]: year(exrp) = 2021; [年月日]：date(exrp) ;

（4）统计2021年答题活跃天数

​ [使用]：多表连接使用 join using( )

（5）合并列

​ [使用]： [2021年]: year(exrp) = 2021; [年月日]：date(exrp) ;

最终结果

select 查询结果 [总活跃月份数; 2021年活跃天数; 2021年试卷作答活跃天数; 2021年答题活跃天数]
from 从哪张表中查询数据[多个join连接的表]
where 查询条件 [level等级是6/7]
order by 对查询结果排序 [按照总活跃月份数、2021年活跃天数降序];
实现过程

（1）需要一个临时表：

with 
    main as(
		#试卷作答记录和题目练习记录
        select distinct
            a.uid,
            date(start_time) as days,
            'exam' as tag
        from user_info a
        left join exam_record b
        using(uid)
        union
        select distinct
            a.uid,
            date(submit_time) as days,
            'question' as tag
        from user_info a
        left join practice_record c
        using(uid)
    )

注意：mysql版本在8.0之前不支持with。如需配置mysql的8.0版本参考

（2）求select列

  • 总活跃月份数
#总活跃月份数 attr
select 
    uid,
    count(distinct date_format(days,'%y%m')) as act_month_total
from main
group by uid
  • 2021年活跃天数
#2021年试卷作答活跃天数 attr1
select
	uid,
	count(distinct(if(year(start_time) = 2021,start_time,null))) as act_days_2021_exam
from main
group by uid

  • 2021年试卷作答活跃天数

  • count(distinct(if(year(date(act_date)) = 2021 and tag = 'exam',act_date,null)))

    利用tag标记是试卷作答记录还是答题作答记录。

#2021年试卷作答活跃天数 attr2
select
    uid,
    count(distinct(if(year(days) = 2021 and tag = 'exam',days,null))) as act_days_2021_exam
from main1
group by uid
  • 2021年答题活跃天数
#2021年答题活跃天数 attr3
select
    uid,
    count(distinct(if(year(days) = 2021 and tag = 'question', days, null))) as act_days_2021_question
from main1
group by uid

（3）合并列

select
    a.uid,
    act_month_total,
    act_days_2021,
    act_days_2021_exam,
    act_days_2021_question
from user_info a
left join attr using(uid) 
left join attr1 using(uid)
left join attr2 using(uid)
left join attr3 using(uid)
where level between 6 and 7
order by act_month_total desc,act_days_2021 desc
求解代码

方法一：

使用 with

with 
    main as(
		#试卷作答记录和题目练习记录
        select distinct
            a.uid,
            date(start_time) as days,
            'exam' as tag
        from user_info a
        left join exam_record b
        using(uid)
        union
        select distinct
            a.uid,
            date(submit_time) as days,
            'question' as tag
        from user_info a
        left join practice_record c
        using(uid)
    ) 
#合并列
select
    a.uid,
    act_month_total,
    act_days_2021,
    act_days_2021_exam,
    act_days_2021_question
from user_info a
left join(
    #总活跃月份数指的是所有年
    select 
        uid,
        count(distinct date_format(days,'%y%m')) as act_month_total
    from main
    group by uid
) attr using(uid)
left join(
    #2021年活跃天数
    select 
        uid,
        count(distinct if(year(days) = 2021,days,null)) as act_days_2021	
    from main
    group by uid
) attr1 using(uid)

left join(
    #2021年试卷作答活跃天数
    select
        uid,
        count(distinct(if(year(days) = 2021 and tag = 'exam',days,null))) as act_days_2021_exam
    from main
    group by uid
) attr2 using(uid)

left join(
    #2021年答题活跃天数
    select
        uid,
        count(distinct(if(year(days) = 2021 and tag = 'question',days,null))) as act_days_2021_question
    from main
    group by uid
) attr3 using(uid)
where level between 6 and 7
order by  act_month_total desc,act_days_2021 desc#按照总活跃月份数、2021年活跃天数降序排序

方法二：

不使用 with

select
    uid,
    count(distinct date_format(days,'%y%m')) as act_month_total,#总活跃月份数指的是所有年
    count(distinct if(year(days) = 2021,days,null)) as act_days_2021,#2021年活跃天数
    count(distinct(if(year(days) = 2021 and tag = 'exam',days,null))) as act_days_2021_exam,#2021年试卷作答活跃天数
    count(distinct(if(year(days) = 2021 and tag = 'question',days,null))) as act_days_2021_question#试卷作答记录和题目练习记录
from user_info
left join(
    select distinct
        uid,
        date(start_time) as days,
        'exam' as tag
    from user_info
    left join exam_record using(uid)
    union
    select distinct
        uid,
        date(submit_time) as days,
        'question' as tag
    from user_info
    left join practice_record using(uid)
) main using(uid)
where level between 6 and 7
group by uid
order by  act_month_total desc,act_days_2021 desc#按照总活跃月份数、2021年活跃天数降序排序
扩展：

前往查看MySQL 嵌套子查询 with子句 from子查询 in子查询 join子查询

酸菜鱼土豆大侠
关注
  • 3
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql一对多，将多条数据合并成一条数据，指定字段数据拼接展示
yuzheh521的博客
02-10 2577
mysql一对多，将多条数据合并成一条数据，指定字段数据拼接展示
mysql sql语句多表合并UNION ALL和UNION
weixin_30735391的博客
10-17 2501
select d1.ID,CAST(d1.ID AS CHAR) AS intId, d1.CODE_TYPE, d1.CODE, d1.CODE_IMG, d1.VALUE from m_dict_code d1 where d1.CODE_TYPE in('USER_TYPE','addSupers') UNION ...
Mysql基础教程（12）：JOIN
最新发布
WwLK123的博客
05-31 617
在 MySQL 中，JOIN 语句用于将数据库中的两个表或者多个表组合起来。
MySQL 合并查询union 查询出的行合并到一个表中
我的博客
02-02 7076
在合并查询中，尤其是二分类的情况，在查询结果是相同列名的时候可以考虑合并查询。先查询出行的结果，再使用union或者union all合并查询结果。另外如果 union 和 order by 一起使用的话要注意先后顺序。
MySQL查询把多列返回结果集拼接成一个字符串
weixin_43236331的博客
11-01 3671
Mysql查询把多列返回结果集拼接成一个字符串代码结果 代码 SELECT u.id, u.NAME, r.NAME AS roleName, GROUP_CONCAT(m.NAME) AS merchName, u.state FROM tbl_user u LEFT JOIN tbl_user_park up ON u.id = up.userid LEFT JOIN tbl_merch m ON up.parkid = m.id LEFT JOIN tbl_role r ON u
关于mysql合并表的详细浅谈
xiaoweids的博客
06-02 884
mysql创建合并表和分区表有点相似，他是mysql 的一种特性，可以把多个结构相同的myisam表合并为一个虚表，存续引擎必须是merge，当从合并表中查询就像从子表中查询一样，和视图有写相似，当创建合并表时也有自己的frm和mrg 文件。合并表比非合并表含有更多的文件描述符，他的访问其实是打开了子表，所以缓存中的数据可能对应有多个文件描述符,在合并表中虽然有可能有相同的值，但是只要查询找到了第一个想要的值，查询就会停止。本篇文章是对mysql中的合并表进行了详细的分析介绍，需要的朋友参考下。
MySQL Union合并查询数据及表别名、字段别名用法分析
09-09
MySQL中的`UNION`和`UNION ALL`是两种用于合并多个`SELECT`查询结果集的方法，它们在处理数据合并时具有重要的作用。本篇文章将深入解析这两种操作以及如何使用表别名和字段别名来简化和优化SQL查询。 1. **MySQL ...
mysql实现查询数据并根据条件更新到另一张表的方法示例
09-09
在MySQL中，有时候我们需要将一个表中的数据查询出来，并根据特定条件更新到另一个表中。这个过程涉及到多表查询和更新操作，对于数据库管理和数据迁移至关重要。以下将详细讲解如何实现这一目标，以及使用到的关键...
MySQL多表查询实例详解【链接查询、子查询等】
09-08
在MySQL数据库中，多表查询是一项基础且至关重要的技能，特别是在处理复杂的数据关系时。本篇文章将深入探讨如何使用链接查询（JOIN）和子查询来实现多表查询，以满足不同场景下的数据检索需求。 首先，我们要理解...
MySQL联表查询基本操作之left-join常见的坑
09-08
在MySQL数据库中，联表查询（也称为JOIN查询）是一种常用的数据检索方法，它允许从多个相关表中合并数据。LEFT JOIN（左连接）是其中一种类型，它返回所有左表（即连接操作的第一个表）的记录，即使在右表（第二个表...
mysql多表联合查询操作实例分析
09-09
MySQL的多表联合查询是数据库操作中非常重要的一个概念，特别是在处理复杂的数据关系时，能够有效地从多个表中提取所需信息。本篇文章将深入探讨这一主题，通过实例分析来阐述其语法、功能以及操作技巧。 首先，让...
mysql如何实现多行查询结果合并成一行
09-10
利用函数：group_concat(),实现一个ID对应多个名称时，原本为多行数据，把名称合并成一行
mysql将多行字段数值合并为一行字段数据
dongyue520的博客
10-24 5654
2.使用分组查询对jh进行分组，然后合并jl的数据，发现在mysql中使用分组查询显示的字段只能是分组查询的字段，一直不理解这个。sql语句：select * from cc as a INNER JOIN cc_copy1 as b on a.jh=b.jh;sql语句：UPDATE cc as a INNER JOIN cc_copy1 as b on a.jh=b.jh set b.#复制name，将cc取名a中表name赋值到cc_copy1取名b中表name。4.插入刚刚查询的合并结果。
MySQL使用UNION ALL将两个查询合并成一个结果
m0_49245543的博客
05-26 2万+
MySQL 查询合并 如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。 注意：两个列表中的字段要一样才可以合并(顺序也要一样) 满足条件： 1、两个select查询的列的数量必须相同； 2、每个列的数据类型需要相似； 1.先写两条select 第一条select： SELECT DATE_FORMAT(add_time, '%Y-%m-%d') as 'add_time',
mysql数据库中把查询的结果列合并（GROUP_CONCAT）
一轮明月半城沙
05-08 1万+
生产中的应用场景： 在数据库中group by 一列查询出若干行数据，sql如下： select * from table group by 列字段; 合并查询出的列： select GROUP_CONCAT（查询的字段 separator ‘；’） from table group by 列字段;
mysql 合并两个查询结果
热门推荐
丕子李
01-11 9万+
合并两个查询结果 查询结果1： select date(complete_time) as time,sum(amt) as amount_out from withdraw where state=3 group by date(complete_time) 查询结果2 select date(update_time) as time,sum(transf
MySQL多个相同结构的表查询并把结果合并放在一起的语句（union all）
angou6476的博客
10-23 1853
union all select * from table1001 where price > 10 union all select * from table1002 where price > 10 union all select * from table1003 where price > 10 order by ID 列的个数要保持一致，...
mysql 把查询结果一列拼接_Mysql中将查询出来的多列的值用逗号拼接
weixin_39742727的博客
12-19 489
烂泥：haproxy学习之手机规则匹配本文由ilanniweb提供友情赞助,首发于烂泥行天下 想要获得更多的文章,可以关注我的微信ilanniweb. 今天我们来介绍下有关haproxy匹配手机的一些规则配置. 一.业务需要 现在根据业务 ...Activity与FragmentFragment是Android honeycomb 3.0新增的概念,Fragment名为碎片不过却和Activity...
MYSQL实现列拼接，即同一个字段，多条记录拼接成一条
weixin_34233856的博客
08-31 1602
一、首先，新建三张表 DROP TABLE IF EXISTS `article`; CREATE TABLE `article` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL COMMENT '标题', `content` text NOT NULL COMM...
mysql中怎么把两张不同的表列合并
07-11
你可以使用MySQL中的JOIN操作来合并两张不同的表列。JOIN操作允许你根据某些条件将两个或多个表中的行连接起来。 以下是一些常用的JOIN操作： 1. INNER JOIN：返回两个表中匹配的行。 ```sql SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; ``` 2. LEFT JOIN：返回左表中的所有行以及与右表中匹配的行。 ```sql SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; ``` 3. RIGHT JOIN：返回右表中的所有行以及与左表中匹配的行。 ```sql SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; ``` 4. FULL JOIN：返回左右表中的所有行。 ```sql SELECT * FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name; ``` 请注意，以上示例假设两个表具有共同的列名，你需要将 `column_name` 替换为实际的列名。通过使用这些JOIN操作，你可以将两张不同的表列合并在一起。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值