精华内容
下载资源
问答
  • 此时可以看到【mobile】的数据后面都添加了一个逗号, 【size】是【mobile】原数据总长度,减去去掉逗号的原数据,加1的长度。 【REPLACE(mobile,’,’,’’)】是【mobile】原数据把逗号替换为空的数据。 ...

    场景

    场景:一行多条数据转为多行。(多用于数据清洗)

    原数据
    在这里插入图片描述
    转换成下图数据
    在这里插入图片描述

    建表数据

    • 创建表
    create TABLE user1(
    id int(11),
    user_name varchar(3),
    overss varchar(5),
    mobile varchar(100)
    );
    
    • 插入数据
    INSERT into user1 VALUES(1,'唐僧','功德佛','121123456,141123456,161123456');
    INSERT into user1 VALUES(2,'猪八诫','净坛使者','12144643321,14144643321');
    INSERT into user1 VALUES(3,'孙悟空','斗战胜佛','12166666666,14166666666,16166666666,18166666666');
    INSERT into user1 VALUES(4,'沙僧','金身罗汉','12198765432,14198765432');
    
    • 查询数据
    select * from user1;
    

    在这里插入图片描述

    • 创建序列表(后续作用讲解)
    CREATE table tb_sequence(id int auto_increment not null ,primary key(id));
    
    • 插入自增长数据(注:此时的场景插入四行,个人理解为有多少逗号就插入逗号数+1行)
    insert into tb_sequence VALUES(),(),(),();
    
    • 查询【tb_sequence】表
    select * from tb_sequence;
    

    在这里插入图片描述

    • 完整sql查询语句
    select user_name,REPLACE(substring(SUBSTRING_INDEX(mobile,',',a.id),
    CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') as mobile 
    from (
    SELECT user_name,CONCAT(mobile,',') as mobile,
    LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size
    from user1 
    ) b join tb_sequence a on a.id<=b.size
    

    可以看到数据转换完成,下面分割步骤。
    在这里插入图片描述

    步骤and思路

    先来看最里面一层

    SELECT user_name,CONCAT(mobile,',') as mobile,
    LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size,
    REPLACE(mobile,',','') // 这一列REPLACE(mobile,',','')结果语句里是没有的,为了便于大家理解
    from user1
    

    注:文章结尾有函数讲解

    • REPLACE()函数用于字符串替换。
    • CONCAT()函数用于字符串拼接。
    • LENGTH()函数用于查询字符串长度。

    此时可以看到【mobile】列的数据后面都添加了一个逗号,
    【size】列是【mobile】原数据总长度,减去去掉逗号的原数据,加1的长度。
    【REPLACE(mobile,’,’,’’)】列是【mobile】列原数据把逗号替换为空的数据。
    注意:如果你的数据不是逗号,这里记得更换。

    • 结果集
      在这里插入图片描述
    • 连接序列化表【tb_sequence】,此时为了直观一些,先不做处理。
    select * from (
    SELECT user_name,CONCAT(mobile,',') as mobile,
    LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size
    from user1 
    ) b join tb_sequence a on a.id<=b.size
    

    重点在于【on a.id<=b.size】,意思是只要【tb_sequence】表【id】列的序号只要小于等于【size】列的长度就笛卡尔积一次,也就是【size】的长度是多少,就显示为多少行数据。
    注意:此时也说明了【tb_sequence】表为什么插入4行序列号。

    • 结果集
      在这里插入图片描述
    • 拆解步骤,每个字段可以对着结果集分别理解Why,后面有注释。
      注:文章结尾有函数讲解
    select user_name,
    SUBSTRING_INDEX(mobile,',',a.id-1) one, // 截取第a.id-1个逗号之前的数据
    
    CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1 tow,// 查询上面的数据的长度+1
    
    SUBSTRING_INDEX(mobile,',',a.id) three, // 截取第a.id个逗号之前的数据
    
    substring(SUBSTRING_INDEX(mobile,',',a.id),CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1) four,// 从three列截取tow列位置之后的数据
    
    REPLACE(substring(SUBSTRING_INDEX(mobile,',',a.id),
    CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') as mobile // 替换掉four列的逗号
    
    from (
    SELECT user_name,CONCAT(mobile,',') as mobile,
    LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size
    from user1 
    ) b join tb_sequence a on a.id<=b.size
    
    • 结果集
      在这里插入图片描述

    结果

    • 完整sql查询语句
    select user_name,REPLACE(substring(SUBSTRING_INDEX(mobile,',',a.id),
    CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') as mobile 
    from (
    SELECT user_name,CONCAT(mobile,',') as mobile,
    LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size
    from user1 
    ) b join tb_sequence a on a.id<=b.size
    

    完成后的结果集
    在这里插入图片描述

    函数

    • CONCAT()函数
    语法:CONCAT(str1,str2,)  
    说明:返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。
    例子:
    SELECT CONCAT('12','ab','!@')      //结果为:12ab!@
    SELECT CONCAT('12','ab','!@',null)      //结果为:null
    
    • LENGTH()函数
    语法:LENGTH(str)CHAR_LENGTH(str)
    说明:都是返回字符串的长度(区别就不说了)。
    例子:
    select LENGTH('156abc')    //结果为6
    select CHAR_LENGTH('156abcdg')   //结果为8
    
    • SUBSTRING()函数
    语法:SUBSTRING(str, pos)  
    说明:SUBSTRING(被截取字段,从第几位开始截取) 
    例子:SELECT SUBSTRING('abcdefg',3)    //结果为 cdefg 
    
    语法:SUBSTRING(str, pos, length)
    说明:SUBSTRING(被截取字段,从第几位开始截取,截取长度) 
    例子:SELECT SUBSTRING('abcdefg',3,2) //结果为 cd
    
    • SUBSTRING_INDEX()函数
    语法:SUBSTRING_INDEX(str,delim,count) 
    说明:SUBSTRING_INDEX(被截取字段,分隔符,关键字出现的次数)
    例子:
    SELECT SUBSTRING_INDEX('15,151,152,16',',',1); //结果是15
    //以第一个逗号为分割截取
    SELECT SUBSTRING_INDEX('15,151,152,16',',',2); //结果是15,151
    //以第二个逗号为分割截取
    SELECT SUBSTRING_INDEX('15,151,152,16',',',-1); //结果是16
    //从后面开始算第一个逗号
    
    • REPLACE()函数
    语法:REPLACE(str , from_str , to_str) 
    说明:把str 中出现from_str 的全部替换为to_str
    例子:SELECT REPLACE('www.jb51.net' , 'w' , 'Ww') //结果是:WwWwWw.jb51.net 
    
    
    展开全文
  • 数据库列转行函数

    千次阅读 2018-07-20 11:39:36
    mysql: 语句:  select u.name1,group_concat(r.rolename,' ') from user1 u left join role r on u.id = r.user_id group by u.name1;  oracle: 语句: ... select listagg(c.username,',') winthin g...

    mysql:

    语句:

              select u.name1,group_concat(r.rolename,' ') from user1 u left join role r on u.id = r.user_id group by u.name1; 

    oracle:

    语句:

            select  listagg(c.username,',') winthin group(order by c.username)  from (select  a.username  from  a left join b on a.username = b.loginname  ) c  group by username;

    在实际开发中遇到一个问题,当username中合并的字符太多是会报错“字符串连接的结果过长”,这是因为,当合并完之后默认的类型是varchar类型,varchar字符长度只有4000,当超过4000时就会报错。所以此方法不适合数据量很大的地方,局限性比较大。

    为了解决上述所遇到的问题换另一种方法即可完美解决,查出来的数据是clob类型:代码如下

        select    xmlagg(xmlparse(content  c.username ||',' wellformed) order by c.username).getclobval() as username     from (select  a.username  from  a left join b on a.username = b.loginname  ) c  group by username;

     

    展开全文
  • [一]、行转1.1、初始测试数据表结构:TEST_TB_GRADEcreatetableTEST_TB_GRADE(IDNUMBER(10)notnull,USER_NAMEVARCHAR2(20CHAR),COURSEVARCHAR2(20CHAR),SCOREFLOAT)createtableTEST_TB_GRADE(IDNUMBER(10)n...

    [一]、行转列

    1.1、初始测试数据

    表结构:TEST_TB_GRADE

    create

    table TEST_TB_GRADE

    (

    ID        NUMBER(10)notnull,

    USER_NAME VARCHAR2(20CHAR),

    COURSE    VARCHAR2(20CHAR),

    SCOREFLOAT

    )

    createtableTEST_TB_GRADE

    (

    ID        NUMBER(10) notnull,

    USER_NAME VARCHAR2(20 CHAR),

    COURSE    VARCHAR2(20 CHAR),

    SCORE     FLOAT

    )

    初始数据如下图:

    0818b9ca8b590ca3270a3433284dd417.png

    1.2、 如果需要实现如下的查询效果图:

    0818b9ca8b590ca3270a3433284dd417.png

    这就是最常见的行转列,主要原理是利用decode函数、聚集函数(sum),结合group by分组实现的,具体的sql如下:

    select t.user_name,

    sum(decode(t.course,'语文', score,null))asCHINESE,

    sum(decode(t.course,'数学', score,null))asMATH,

    sum(decode(t.course,'英语', score,null))asENGLISH

    fromtest_tb_grade t

    groupbyt.user_name

    orderbyt.user_name

    selectt.user_name,

    sum(decode(t.course,'语文', score,null))asCHINESE,

    sum(decode(t.course,'数学', score,null))asMATH,

    sum(decode(t.course,'英语', score,null))asENGLISH

    fromtest_tb_grade t

    groupbyt.user_name

    orderbyt.user_name

    1.3、延伸

    如果要实现对各门功课的不同分数段进行统计,效果图如下:

    0818b9ca8b590ca3270a3433284dd417.png

    具体的实现sql如下:

    select t2.SCORE_GP,

    sum(decode(t2.course,'语文', COUNTNUM,null))asCHINESE,

    sum(decode(t2.course,'数学', COUNTNUM,null))asMATH,

    sum(decode(t2.course,'英语', COUNTNUM,null))asENGLISH

    from(

    selectt.course,

    casewhent.score  <60then'00-60'

    whent.score >=60andt.score <80then'60-80'

    whent.score >=80then'80-100'endasSCORE_GP,

    count(t.score)asCOUNTNUM

    FROMtest_tb_grade t

    groupbyt.course,

    casewhent.score  <60then'00-60'

    whent.score >=60andt.score <80then'60-80'

    whent.score >=80then'80-100'end

    orderbyt.course ) t2

    groupbyt2.SCORE_GP

    orderbyt2.SCORE_GP

    selectt2.SCORE_GP,

    sum(decode(t2.course,'语文', COUNTNUM,null))asCHINESE,

    sum(decode(t2.course,'数学', COUNTNUM,null))asMATH,

    sum(decode(t2.course,'英语', COUNTNUM,null))asENGLISH

    from(

    selectt.course,

    casewhent.score  <60then'00-60'

    whent.score >=60andt.score <80then'60-80'

    whent.score >=80then'80-100'endasSCORE_GP,

    count(t.score)asCOUNTNUM

    FROMtest_tb_grade t

    groupbyt.course,

    casewhent.score  <60then'00-60'

    whent.score >=60andt.score <80then'60-80'

    whent.score >=80then'80-100'end

    orderbyt.course ) t2

    groupbyt2.SCORE_GP

    orderbyt2.SCORE_GP

    [二]、列转行

    1.1、初始测试数据

    表结构:TEST_TB_GRADE2

    create

    table TEST_TB_GRADE2

    (

    ID         NUMBER(10)notnull,

    USER_NAME  VARCHAR2(20CHAR),

    CN_SCOREFLOAT,

    MATH_SCOREFLOAT,

    EN_SCOREFLOAT

    )

    createtableTEST_TB_GRADE2

    (

    ID         NUMBER(10) notnull,

    USER_NAME  VARCHAR2(20 CHAR),

    CN_SCORE   FLOAT,

    MATH_SCORE FLOAT,

    EN_SCORE   FLOAT

    )

    初始数据如下图:

    0818b9ca8b590ca3270a3433284dd417.png

    1.2、 如果需要实现如下的查询效果图:

    0818b9ca8b590ca3270a3433284dd417.png

    这就是最常见的列转行,主要原理是利用SQL里面的union,具体的sql语句如下:

    select user_name,

    '语文' COURSE , CN_SCORE

    as SCORE

    from test_tb_grade2

    unionselectuser_name,'数学'COURSE, MATH_SCOREasSCOREfromtest_tb_grade2

    unionselectuser_name,'英语'COURSE, EN_SCOREasSCOREfromtest_tb_grade2

    orderbyuser_name,COURSE

    selectuser_name,'语文'COURSE , CN_SCOREasSCOREfromtest_tb_grade2

    unionselectuser_name,'数学'COURSE, MATH_SCOREasSCOREfromtest_tb_grade2

    unionselectuser_name,'英语'COURSE, EN_SCOREasSCOREfromtest_tb_grade2

    orderbyuser_name,COURSE

    也可以利用【insert all into ... select】来实现,首先需要先建一个表TEST_TB_GRADE3:

    create

    table TEST_TB_GRADE3

    (

    USER_NAME VARCHAR2(20CHAR),

    COURSE    VARCHAR2(20CHAR),

    SCOREFLOAT

    )

    createtableTEST_TB_GRADE3

    (

    USER_NAME VARCHAR2(20 CHAR),

    COURSE    VARCHAR2(20 CHAR),

    SCORE     FLOAT

    )

    再执行下面的sql:

    insert

    all

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'语文', CN_SCORE)

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'数学', MATH_SCORE)

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'英语', EN_SCORE)

    selectuser_name, CN_SCORE, MATH_SCORE, EN_SCOREfromtest_tb_grade2;

    commit;

    insertall

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'语文', CN_SCORE)

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'数学', MATH_SCORE)

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'英语', EN_SCORE)

    selectuser_name, CN_SCORE, MATH_SCORE, EN_SCOREfromtest_tb_grade2;

    commit;

    别忘记commit操作,然后再查询TEST_TB_GRADE3,发现表中的数据就是列转成行了。

    展开全文
  • 数据库 列转行

    2021-12-28 15:43:01
    转换前 转换后 select name,'语文' as subject,China as number from student UNION select name,'数学' as subject,Math as number from student UNION select name,'英语' as subject,english as number ...

    转换前

    在这里插入图片描述

    转换后

    在这里插入图片描述

    select name,'语文' as subject,China as number from student
    
    UNION 
    select name,'数学' as subject,Math as number from student
    
    UNION 
    select name,'英语' as subject,english as number from student
    
    UNION 
    select name,'体育' as subject,sports as number from student
    
    展开全文
  • 列转行第一种方法,代码见下: select name, sum(decode(course, '数学', score)) 数学, sum(decode(course, '英语', score)) 英语, sum(decode(course, '语文', score)) 语文 from tb_student group by name;...
  • 列转行:利用max(case when then) max—聚合函数 取最大值 (case course when ‘语文’ then score else 0 end) —判断 as 语文—别名作为列名 SELECT `name`, MAX( CASE WHEN course='\u8bed\u6587' THEN ...
  • 首先需要知道一个函数:concat_ws select ord,num,concat_ws('=',ord,num) from cx.over_test; 如果我们需要这样查询一条数据: 当给出一个数字,我们首先需要判断其是ord还是num,然后再取其对应的值的话,显示...
  • 关于SQL SERVER 数据库列转行的研究 :两种不同方法的比较,代码效率的比较
  • 结果数据库 通过FOR XML PATH把skill按-拼接在一起(-缠绕-寄生-蛛网束缚),然后使用STUFF函数把第一个-符号去掉。 SQL Code select A.* into #result from ( select '唐三' as name,'缠绕' as skill ...
  • 目录结构如下:行转列列转行[一]、行转列1.1、初始测试数据表结构:TEST_TB_GRADEcreatetableTEST_TB_GRADE(IDNUMBER(10)notnull,USER_NAMEVARCHAR2(20CHAR),COURSEVARCHAR2(20CHAR),SCOREFLOAT)createtableTEST_TB_...
  • oracle的列转行函数

    2018-04-18 09:34:59
    SELECT TRIM(',' FROM SYS.STRAGG(A_NAME||NVL2(A_NAME,',','')))as nams FROM A_TEMP
  • 1.创建数据库表,为表字段添加数据 #创建数据库表结构 create table tb_student_grade( id int(10) not null auto_increment primary key, s_name varchar(20) default null, course varchar(20) default null, ...
  • 数据库之行转列与列转行方法

    万次阅读 2018-06-04 23:51:28
    --创建纵表CREATE TABLE TABLE_A ( NAME VARCHAR2(20), SUBJECT VARCHAR2(20), SCORE NUMBER(22,0));
  • 行转列与列转行的操作,在数据查询中还是用得比较多的。可以将列名转换为列值,也可以将列值转换为列名。使相同的数据以多姿态的形式呈现出来,让用户能更直观的感受数据。行转列:简单表演示先来看看效果:这是原表...
  • ORACLE数据库列转行(实现字段合并) SELECT T.SCATEG,TO_CHAR(wm_concat(SNAME)) FROM STAR T GROUP BY T.SCATEG; DB2数据库列转行(实现字段合并) SELECT LISTAGG(T.SNAME,’,’) FROM STAR T GROUP BY T....
  • 行转 将图1做成图2的效果 图1: 图2: 创建数据表: CREATE TABLE `TEST_TB_GRADE` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `USER_NAME` varchar(20) DEFAULT NULL, `COURSE` varchar(20) DEFAULT NULL, `...
  • [数据库]Oracle行转(使用pivot函数)0 2015-07-03 12:00:14在日常使用中,经常遇到这样的情况,需要将数据库中行转化成显示,如转化为这个时候,我们就需要使用pivot函数百度后,参考网址...完成了以下操作with temp...
  • mssql 数据库表行转列,列转行终极方案,需要的朋友可以参考下。
  • 在处理数据的时候,因为数据源在Excel文件中,本人Excel文件的处理方法不是太会,加上朋友说在Excel中处理这样的数据很麻烦,我就想着干脆导入到数据库中通过代码解决这个问题,感兴趣的可以找我拿数据源自己试试。...
  • sql列转行

    2021-03-10 06:53:37
    ,[subject],[Source]) values (N'张三',N'英语',100) Go --交叉表语句的实现: --用于:交叉表的数是确定的 select name,sum(case subject when '数学' then source else 0 end) as '数学', sum(case...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 9,397
精华内容 3,758
关键字:

数据库的列转行