精华内容
下载资源
问答
  • mysql行转列(多行转一列

    千次阅读 2021-01-19 05:17:40
    场景比如说个订单对应多条数据,当状态(status)=1的时候, 数量(num)=25,当状态(status)=2的时候, 数量(num)=45,现在想用条sql记录下不同状态对应的数量为多少,如下图所示:期待结果->sql语句示例SELECT ...

    场景

    比如说一个订单对应多条数据,当状态(status)=1的时候,  数量(num)=25,当状态(status)=2的时候,  数量(num)=45,现在想用一条sql记录下不同状态对应的数量为多少,如下图所示:

    596570b04cf7107b2fcae6be0f19f527.png

    期待结果->

    17793157c54c5de7900ff2af1ede3400.png

    sql语句示例

    SELECT trades_id,

    MAX(CASE WHEN apply_status=1 THEN pay_change ELSE 0 END) 'num_status1',

    MAX(CASE WHEN apply_status=2 THEN pay_change ELSE 0 END) 'num_status2'

    FROM b_balance_detail

    WHERE site_id=100190

    AND apply_status!=4 AND trades_id = 1001901531475622312

    //上述sql是查询了一个订单的情况,当多个时应当加个group by进行分组查询 如下:

    SELECT trades_id,

    MAX(CASE WHEN apply_status=1 THEN pay_change ELSE 0 END) 'num_status1',

    MAX(CASE WHEN apply_status=2 THEN pay_change ELSE 0 END) 'num_status2'

    FROM b_balance_detail

    group by trades_id

    展开全文
  • 个是test表 第二个是course表 2.想要的数据结构 3.编写的sql语句 select course.name,concat('分层班课程:',group_concat(test.score separator '、')) '课程' from test LEFT JOIN course on course...
    1.原数据结构
    第一个是test表
    第二个是course表

    2.想要的数据结构
    

    3.编写的sql语句
    select course.name,concat('分层班课程:',group_concat(test.score separator '、')) '课程' 
    from test LEFT JOIN course
    on course.id = test.name
    group by course.name

     

    展开全文
  • hive 行转列/转行 多行转一行/一行转多行 Mysql 创建表语句: CREATE TABLE student_score( id BIGINT PRIMARY key auto_increment, s_name VARCHAR(20) , s_sub VARCHAR(20), s_score INT ); insert into student_...

    hive 行转列/列转行 多行转一行/一行转多行

    Mysql

    创建表语句:

    CREATE TABLE student_score(
    id BIGINT PRIMARY key auto_increment,
    s_name VARCHAR(20) ,
    s_sub VARCHAR(20),
    s_score INT 
    );
    
    insert into student_score values(null,'张三','数学',90);
    insert into student_score values(null,'张三','语文',85);
    insert into student_score values(null,'张三','英语',92);
    insert into student_score values(null,'李四','数学',88);
    insert into student_score values(null,'李四','语文',91);
    insert into student_score values(null,'李四','英语',99);
    insert into student_score values(null,'王五','数学',100);
    insert into student_score values(null,'王五','语文',82);
    insert into student_score values(null,'王五','英语',88);
    
    
    select * from student_score;
    

    在这里插入图片描述

    行转列

    业务场景,按照上图看的话很难只看同学们的数学成绩或者语文成绩,所以要按照以下格式展示:

    s_name数学语文英语
    张三908592
    李四889199
    李四1008288

    思路:
    第一步可以将每一行根据学科s_sub分成数学,语文,英语三列 , sql语句:

    case  s_sub  when '数学' then s_score else 0 end 数学
    

    完整sql:

    select s_name,
    case s_sub when '数学' then s_score else 0 end 数学,
    case s_sub when '语文' then s_score else 0 end 语文,
    case s_sub when '英语' then s_score else 0 end 英语
    from student_score
    

    结果:
    在这里插入图片描述
    最后,需要按照s_name为维度group by聚合一下

    select s_name,
    sum(case s_sub when '数学' then s_score else 0 end) 数学,
    sum(case s_sub when '语文' then s_score else 0 end) 语文,
    max(case s_sub when '英语' then s_score else 0 end) 英语
    from student_score group by s_name
    

    最终结果:
    在这里插入图片描述
    如果不是int类型而是 varchar 那么上面是行不通的,因为sum里面不能跟字符串,需要用group_concat()函数替代sum,可先看多行转一行之后再看下方sql

    select s_name,
    GROUP_CONCAT(case s_sub when '数学' then  CAST(s_score AS char) else null end) 数学,
    GROUP_CONCAT(case s_sub when '语文' then  CAST(s_score AS char) else null end) 语文,
    GROUP_CONCAT(case s_sub when '英语' then  CAST(s_score AS char) else null end) 英语
    from student_score group by s_name
    

    多行转一行

    业务场景,以张三为例,将张三的三个成绩拼接成字符串 放到一个单元格中显示:

    '张三'    '数学:90,语文:85,英语92'
    

    上述 类似于

    select s_name, sum(s_score) from student_score group by s_name
    

    sum 就是一个聚合函数,只不过它是将所有的数字相加得到结果,根据上面业务场景,此时要用到一个函数 group_concat ,类似于sum 但是它的作用是将聚合后的数据进行字符拼接
    group_concat (a,b,c,… separator ’ ‘) 或者
    group_concat (a,b,c,…)
    group_concat 中的参数是可变参数,多个参数拼接,最后一个speparator xxx 是多行数据拼接后的分隔符,如果不写默认的分隔符是’,'逗号.并且记住要用group by 维度聚合,不写group by 会拼接所有的行
    完整的sql:

    select s_name,group_concat(s_sub,':',s_score separator '@') all_score from  student_score group by s_name
    

    结果:
    在这里插入图片描述

    列转行

    首先要用到上面行转列的数据

    create table student_score2 as 
    select s_name,
    sum(case s_sub when '数学' then s_score else 0 end) 数学,
    sum(case s_sub when '语文' then s_score else 0 end) 语文,
    max(case s_sub when '英语' then s_score else 0 end) 英语
    from student_score group by s_name
    

    在这里插入图片描述
    下面是实现列转行的sql

    SELECT
    	s_name,
    	'数学' AS s_sub,
    	数学 AS s_score
      FROM s_score
    UNION
    SELECT
    	s_name,
    	'语文' AS s_sub,
    	语文 AS s_score
      FROM s_score
    UNION
    SELECT
    	s_name,
    	'英语' AS s_sub,
    	英语 AS s_score
      FROM s_score
    

    结果
    在这里插入图片描述

    一行转多行

    一行转多行在关系型数据库中是有点儿矛盾的,因为关系型数据库设计原则就是单条数据不能再分,如果需要再分是外部(hive/java程序)分完之后进入关系型数据库的,虽然矛盾,但是也是可以实现的.
    创建表:

    create table student_score3 as
    select s_name,group_concat(s_sub,':',s_score separator '@') all_score 
    from  student_score group by s_name
    

    先sql再说思路:

    SELECT
     a.s_name,
     substring_index( substring_index( a.all_score, '@', b.id), '@',- 1 )
    FROM
     student_score3 a
     JOIN account b ON 
     b.id <= ( length( a.all_score ) - length( replace( a.all_score, '@', '' ) ) + 1 );
    

    account表是作为辅助结构的提供1-n个自增长数字
    在这里插入图片描述
    查询结果:
    在这里插入图片描述

    思路:(根据sql思想一步步替换成可执行的sql)

    ①根据张三的一条数据来看:首先,把张三分成三条,三是all_score中想切分成的元素个数决定的

    name    index          all_score
    张三      1         数学:90@语文:85@英语:92
    张三      2         数学:90@语文:85@英语:92
    张三      3         数学:90@语文:85@英语:92
    

    上图sql思想是:

    select a.s_name, allscore from a join b on n <= all_score_arr.size(); n>=1
    --这样子可以根据n的个数join出n条数据.
    

    ②转化成下面我们想要的这样:

    name    index       score
    张三      1         数学:90
    张三      2         语文:85
    张三      3         英语:92
    

    如果把all_score看成一个数组的话,刚好score就是取的all_score_arr的第index个元素
    可以得出sql的思路:

    select a.s_name,all_score_arr[n] from a join b on n <= all_score_arr.size();
    

    如果把上面的sql转换成mysql可以执行的sql语句,就能完成多行转一行的目的.

    ③初步思路有了以后,先看mysql的函数
    先是函数 substring_index(str,sp,x)
    str是要切分的字符串,sp指分隔符,x是int类型通过下面sql

    select substring_index(all_score,'@',x) from student_score3;
    

    可以看规律 x 为1 结果是’数学:90’,x为2 结果是’数学:90@语文85’(从右往左取)
    x为-1 结果是’英语:90’,x为-2 结果是’语文:85@英语;92’(从左往右取)
    所以要用两个substring_index()函数来实现类似于将字符串all_score根据@切分成数组再根据all_score_arr索引取元素.
    substring_index( substring_index( all_score, ‘@’, n), ‘@’,- 1 )
    n= 1时 取的是’数学:90’
    n = 2时 取的是’语文:85’

    这就完成了模拟数组取元素
    所以all_score_arr[n] 可以用 substring_index( substring_index( all_score, ‘@’, n), ‘@’,- 1 )替代:

    select a.s_name,substring_index( substring_index( all_score, '@', n), '@',- 1 )
    from a join b on n <= all_score_arr.size();
    

    n代表着整数可以通过另外一个表的自增主键来替代,b.id就代表了从1-n的整数,得到下面sql:

    select a.s_name,substring_index( substring_index( all_score, '@', b.id), '@',- 1 )
    from a join b on b.id <= all_score_arr.size();
    

    将a与b与实际的表进行替换

    select a.s_name,substring_index(substring_index( all_score, '@', b.id), '@',- 1 )
    from student_score2 a join account b on b.id <= all_score_arr.size();
    

    all_score_arr.size()也就是元素的个数应该如何表示呢?
    可以通过(分隔符+1)来表示元素个数,
    length( a.all_score ) 是字符长度 length( replace( a.all_score, ‘@’, ‘’ ) ) 是去掉分隔符后的长度
    两者相减就得到分隔符数,分隔符数加1就是元素个数
    所以arr.size() 可以用 length( a.all_score ) - length( replace( a.all_score, ‘@’, ‘’ ) ) + 1 表示
    替换后得到最终结果:

     select a.s_name,substring_index( substring_index( a.all_score, '@', b.id+ 1 ), '@',- 1 ) 
     from a join b on b.id<= ( length( a.all_score ) - length( replace( a.all_score, '@', '' ) ) + 1 );
    
    展开全文
  • mysql将某的值转化为一行

    千次阅读 2019-02-21 17:41:16
    mysql中可以行转列。通过group_concat()函数。 如:select status,...将bill_num的值转化为一行,默认用,隔开 要设置指定的间隔符号,如下所示,指定用&amp;隔开 select status,group_concat(name s...

    mysql中可以行转列。通过group_concat()函数。

    如:select status,group_concat(bill_num) from mall_member_order group by status;

    将bill_num列的值转化为一行,默认用,隔开

    要设置指定的间隔符号,如下所示,指定用&隔开

    select status,group_concat(name separator '&') from mall_member_order group by status;

    展开全文
  • mysql行转列转行示例

    千次阅读 2021-01-28 06:52:53
    行转列:将原本同一下多行的不同内容作为多个字段,输出对应内容。效果图:数据库表中的内容:转换后:可以看出,这里行转列是将原来的f_subject字段的多行内容选出来,作为结果集中的不同,并根据f_...
  • MySQL行转列函数

    万次阅读 2019-11-04 09:07:51
    好久没写SQL语句,今天看到问答中的个问题,拿来研究一下。 问题链接:关于Mysql 的分级输出问题 情景简介 学校里面记录成绩,每个人的选课不一样,而且以后会添加课程,所以不需要把所有课程当作。数据表里面...
  • 篇文章搞定mysql行转列(7种方法) 和 转行
  • MySQL中将一列以逗号分隔的值行转

    万次阅读 2019-01-26 16:46:08
    一列中存储了多个属性值。如下表 pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT 一般有这两种常见需求: 得到所有的不重复的值,如 value ...
  • 文章出处:mysql多行多列合并为一行一列 mysql> select * from aa; +------+------+ | id| name | +------+------+ |1 | 10| |1 | 20| |1 | 20| |2 | 20| |3 | 200 | |3 | 500 | +------+------+ ...
  • mysql行转列转行

    万次阅读 多人点赞 2016-09-01 21:17:48
    mysql行转列转行
  • MySQL 重温SQL——行转列转行

    万次阅读 2019-04-11 15:10:23
    行转列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现,也可以通过 SQL SERVER 的运算符PIVOT来实现。用传统的方法,比较好理解。层次清晰,而且比较习惯。 但是PIVOT 、UNPIVOT...
  • 行转列-转换前: 行转列-转换后: 1、使用case...when....then 进行行转列 SELECT userid, SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文', SUM(CASE `subject` WHEN '数学' THEN score ...
  • Mysql 行转列转行

    千次阅读 2018-06-19 15:59:49
    如果我想知道每位学生的每科成绩,而且每个学生的全部成绩排成一行,这样方便我查看、统计,导出数据 SELECT UserName, MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文', MAX(CASE Subject WHEN ...
  • mysql行转列转换

    万次阅读 多人点赞 2017-08-23 16:16:38
    其中最为头痛的就是多行,动态的列行转换。最近在研究这些行里转换,还是从最为简单的行列转换开始。sql 脚本 -- 创建表 学生表 CREATE TABLE `student` ( `stuid` VARCHAR(16) NOT NULL COMMENT '学号', `...
  • mysql实现将一行拆分组合

    千次阅读 2019-09-17 17:27:27
    . 原始表中的数据:可以看到company_id与operate_area_ids字段之间的关系为1:n 二. 拆分多行实现1:1关系 SELECT a.company_id AS company_id, substring_index( substring_index( a.operate_area_ids, '...
  • mysql 一行转

    千次阅读 2016-02-18 09:40:28
    -- http://cenalulu.github.io/mysql/column-row-reverse/ -- http://www.uncletoo.com/html/mysql/1060.html SELECT ID,mSize, substring_index(substring_index(m.mSize,',',num),',',-1) splitText  from ...
  • mysql 行转列转行实例详解

    千次阅读 2020-07-10 11:26:10
    这篇文章主要介绍了mysql 行转列转行实例详解的相关资料,需要的朋友可以参考下 mysql行转列转行语句不难,不做多余解释了,看语句时,从内往外句剖析。
  • MySql 将查询结果多合并成一行GROUP_CONCAT GROUP_CONCAT(expr) 该函数返回带有来自一个组的连接的非NULL值的字符串结果。其完整的语法如下所示: GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER ...
  • MySQL行转列实现和总结

    千次阅读 2018-07-06 14:57:40
    行转列实例1、准备数据CREATE TABLE tb(`cname` VARCHAR(10),cource VARCHAR(10),score INT) ENGINE=INNODB; INSERT INTO tb VALUES('张三','语文',74); INSERT INTO tb VALUES('张三','数学',83); INSERT ...
  • mysql 行转列 (带日期)

    千次阅读 2017-11-11 16:51:09
    从网上找了很多行转列的。基本都是2行转列。不带日期分组的。 借鉴了另个哥们的文章,实现了自己想要的结果,写出来大家可以参考。以后自己遇到同样情况,也可以有个备份 借鉴的地址为 ...
  • mysql 单表行转列,两表级联行转列

    千次阅读 2019-06-10 16:55:52
    参考文章:... 、单表行转列 数据表: 转行:利用max(case when then) max---聚合函数取最大值 (casecoursewhen'语文'thenscoreelse0end)---判断 as 语文---别名作为列名 ...
  • 行转列首先我们先创建个表 表如下 CREATE TABLE test_hjsjy_1( `id` int(10) NOT NULL AUTO_INCREMENT, `user_name` varchar(20) DEFAULT NULL, `course` varchar(20) DEFAULT NULL, `score` float DEFAULT...
  • 转行:利用max(case when then) max---聚合函数 取最大值 (case course when '语文' then score else 0 end) ---判断  as 语文---别名作为列名   SELECT `name`, MAX( CASE WHEN course='语文
  • MySQL -- 行转列的两种方法

    万次阅读 2018-12-23 02:21:39
    MySQL中行转列是很常用的,本文给出了两种行转列的方法。 两张表: 左表:product ,右表:cate SQL语句: 第种方法:IF()语句 SELECT cid, MAX(IF(mon ='月份',num,0)) '月份', MAX(IF(mon ='二月份...
  • MYSQL实现行转列的三种方式

    千次阅读 2020-06-23 10:01:08
    题目 ​ 给定 student_score 表,内容如下: select * from student_score; nane subject score ------------------------------- ...​ 要求用条 sql 语句查出如下结果: 姓名 语文 数学 英语 张三
  • MySQL 实现行转列SQL

    万次阅读 多人点赞 2018-06-03 21:52:36
    概述好久没写SQL语句,今天看到问答中的个问题,拿来研究一下。问题链接:关于Mysql 的分级输出问题情景简介学校里面记录成绩,每个人的...数据库表数据:处理后的结果(行转列):方法:这里可以使用Max,也可...
  • mysql 一行数据+标题转换为两列

    千次阅读 2016-04-06 17:15:38
    -- 如何把一行数据+标题转换为两列? SELECT 'aa' name1, 'bb' name2, 'cc' name3, 'dd' name4 FROM DUAL; -- 转换后的效果。此脚本性能较差, -- 若把dual换为一个子查询,则要查询4次,有多少,就要查询次 ...
  • mysql逻辑分组行转列

    千次阅读 2020-06-28 15:48:44
    -------------------------------------------------------------------------------------------- 如下SQL试错: (个人理解)根据name分组,则name相同的只应该显示一行,但是有可能name相同的存在有多行,MySQL就...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 330,499
精华内容 132,199
关键字:

mysql一列转成一行

mysql 订阅