精华内容
下载资源
问答
  • Oracle数据库列转行的两种方法
    千次阅读
    2020-12-23 06:24:41

    1.创建tb_student表

    create table tb_student(

    id number(4) ,

    name varchar2(20),

    course varchar2(20),

    score number(5,2)

    );

    insert into tb_student values(1,'张三','语文',81);

    insert into tb_student values(2,'张三','数学',75);

    insert into tb_student values(3,'李四','语文',86);

    insert into tb_student values(4,'李四','数学',90);

    insert into tb_student values(5,'王五','语文',81);

    insert into tb_student values(6,'王五','数学',100);

    insert into tb_student values(7,'王五','英语',90);

    commit;

    表如下图:

    2.列转行第一种方法,代码见下:

    select

    name,

    sum(decode(course, '数学', score)) 数学,

    sum(decode(course, '英语', score)) 英语,

    sum(decode(course, '语文', score)) 语文

    from

    tb_student

    group by name;

    3.列转行第二种方法,代码见下:

    select

    name,

    sum(case course when '数学' then score else null end) 数学,

    sum(case course when '英语' then score else null end) 英语,

    sum(case course when '语文' then score else null end) 语文

    from

    tb_student

    group by name;

    最终结果如下图所示:

    decode语法:

    decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

    更多相关内容
  • 关于SQL SERVER 数据库列转行的研究 :两种不同方法的比较,代码效率的比较
  • 行转列及列转行查询

    千次阅读 2021-02-05 19:12:19
    开发过程中常遇到行转列或是列转行的问题,即需要将数据库中一张表信息进行行转列操作,再将每列(即每个字段)作为与其他表进行联表查询的字段进行显示。一、行转列:将原来同一列下多行的不同内容作为多个字段,输出...

    开发过程中常遇到行转列或是列转行的问题,即需要将数据库中一张表信息进行行转列操作,再将每列(即每个字段)作为与其他表进行联表查询的字段进行显示。

    一、行转列:将原来同一列下多行的不同内容作为多个字段,输出对应内容

    数据库表中的内容:

    9d2fbbd120b16ba54ab45db17360c00d.png

    转换后:

    42ffe791e9cd005169ff4f78fc2192c8.png

    解析:

    行转列是将原来的subject字段的多行内容选出来,作为结果集中的不同列,并根据code进行分组显示对应的score

    mysql> select s.name 姓名,s.code 学号,

    -> sum(if(e.subject='语文',e.score,0)) 语文成绩,

    -> sum(if(e.subject='数学',e.score,0)) 数学成绩,

    -> sum(if(e.subject='英语',e.score,0)) 英语成绩

    -> from student s

    -> left join exam e

    -> on s.code=e.code

    -> group by s.code;

    ①sum()函数是为了能够使用group by根据code进行分组,因为每一个student.code对应的exam.subject='语文'的记录只有一条,所以sum()的值就等于对应那一条记录的exam.score的值。

    (正常情况下此记录都是只有一条,当然如果有多条的话,可以使用sum()、max()、avg()、min()等聚合函数也都可以达到行转列的效果)

    ②if(e.subject='语文',e.score,0)作为条件,即对所有经过group by s.code分组的e.subject='语文'的记录的e.score字段进行SUM()、MAX()、MIN()、AVG()操作,如果e.score没有值则默认为0。

    (当然使用case when then作为条件进行信息提取也是OK的)

    二、列转行:将sid的每个科目分数分散成一条记录显示

    数据库中的表:

    e8006d273895db580c9035ac66545f55.png

    转换后:

    cef7d579946ff891ee95dafeb54ab34a.png

    解析:

    使用union all将每个sname对应的多个科目的成绩的结果集加起来

    mysql> select sname,sid,'语文' as subject,语文 as score from transcript

    -> union all

    -> select sname,sid,'数学' as subject,数学 as score from transcript

    -> union all

    -> select sname,sid,'英语' as subject,英语 as score from transcript

    -> order by sname;

    UNION与UNION ALL的区别:

    1.对重复结果的处理:UNION会去掉重复记录,UNION ALL不会;

    2.对排序的处理:UNION会排序,UNION ALL只是简单地将两个结果集合并;

    3.效率方面的区别:因为UNION 会做去重和排序处理,因此效率比UNION ALL慢很多

    展开全文
  • [一]、行转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,发现表中的数据就是列转成行了。

    展开全文
  • 行转 将图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, `...

    MySql

    行转列
    将图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,
      `SCORE` float DEFAULT '0',
      PRIMARY KEY (`ID`)
    ) ;
    

    导入数据:

    insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE)  values
    ("张三", "数学", 34),
    ("张三", "语文", 58),
    ("张三", "英语", 58),
    ("李四", "数学", 45),
    ("李四", "语文", 87),
    ("李四", "英语", 45),
    ("王五", "数学", 76),
    ("王五", "语文", 34),
    ("王五", "英语", 89);
    

    行转列代码实现:

    select username,
    	max(case course when '数学' then score else 0 end) 数学,
    	max(case course when '语文' then score else 0 end) 语文,
    	max(case course when '英语' then score else 0 end) 英语
    from test_tb_grade
    group by username;
    

    hive

    原效果:
    在这里插入图片描述
    代码实现:

    select country,gender,count(*) from customer_details group by country,gender;
    

    行转列效果:
    方法一、
    在这里插入图片描述
    代码实现:

    select country,
    	sum (case gender when 'Male' then 1 else 0 end) as male,
    	sum (case gender when 'Female' then 1 else 0 end) as female
    from customer_details group by country;
    

    方法二、
    在这里插入图片描述
    代码实现:

    with
    t1 as (select country,size(collect_list(gender)) as male from customer_details where gender='Male' group by country),
    t2 as (select country,size(collect_list(gender)) as Female from customer_details where gender='Female' group by country)
    select t1.country,t1.male,t2.Female from t1 inner join t2 on t1.country=t2.country;
    
    展开全文
  • sql查询结果列转行

    2021-10-19 10:47:38
    SQL查询结果列转行 正常查询的结果如下图: 希望得到的结果如下图: SQL语句如下: SELECT SNAME, SUM(CASE WHEN CNAME = 'JAVA' THEN SCORE ELSE 0 END) JAVA, SUM(CASE WHEN CNAME = 'MYSQL' THEN SCORE ELSE 0...
  • --列转行查询 select id, name, jidu, xiaoshou from Fruit unpivot(xiaoshou for jidu in(q1, q2, q3, q4)) 注意: unpivot没有聚合函数,xiaoshou、jidu字段也是临时的变量。等同于下面的SQL: ...
  • 数据库中的行转列 与 列转行

    千次阅读 2020-08-06 11:15:00
    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, ...
  • sql查询:行转列和 列转行

    千次阅读 2020-10-31 21:32:34
    一、行转 将原本同一下多行的不同内容作为多个字段,输出对应内容。 表及数据sql: CREATE TABLE `tb_score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(20) NOT NULL COMMENT '用户id', ...
  • 此时可以看到【mobile】的数据后面都添加了一个逗号, 【size】是【mobile】原数据总长度,减去去掉逗号的原数据,加1的长度。 【REPLACE(mobile,’,’,’’)】是【mobile】原数据把逗号替换为空的数据。 ...
  • mssql 数据库表行转列,列转行终极方案,需要的朋友可以参考下。
  • * 各位大佬我现在就是这样 select出来有数据的 !... * 然后我PIVOT 的话就会报错 !...*他这边PIVOT 的in里面的类型要和我查出来的dl_name数量对应嘛 他好多数量都不是固定的 !...* 我语法有问题吧 他这个的正确写法应该是...
  • 因此用到了逗号分隔列转行的方法。目前该方法只适合在oracle数据库中使用。该方法只需要sql语句就可以实现列转行。  下面给出该方法的示例: select a,b,c from(with test as (select ‘aaa’ a,’bbb’ b,’1,2,3...
  • 如果我们需要这样查询一条数据: 当给出一个数字,我们首先需要判断其是ord还是num,然后再取其对应的值的话,显示此时这个表就不满足了,我们必须将其转换为: SELECT r, regexp_split_to_table( concat_ws ( ',',...
  • 最新在做一个需求,需要用到列转行函数,随手分享一下: 需求展示结果: 原数据:    需求大体描述就是,对SECURITYID进行聚合,一个SECURITYID有多个TYPECODE记录的,转为一行显示,逗号隔开,也就是所说的...
  • 行转列与列转行的操作,在数据查询中还是用得比较多的。可以将列名转换为列值,也可以将列值转换为列名。使相同的数据以多姿态的形式呈现出来,让用户能更直观的感受数据。行转列:简单表演示先来看看效果:这是原表...
  • select * from (select name, nums as "Purchase Frequency"from demo t)pivot xml (sum(nums) for name in (any))如你所见, NAME_XML 是 XMLTYPE,其中根元素是 。每个值以名称-值元素对的形式表示。你可以使用...
  • oracle的列转行函数

    2018-04-18 09:34:59
    SELECT TRIM(',' FROM SYS.STRAGG(A_NAME||NVL2(A_NAME,',','')))as nams FROM A_TEMP
  • MySQL查询结果行转列、列转行

    千次阅读 2021-01-25 14:42:08
    一、行转有如图所示的表,现在希望查询的结果将行转成建表语句如下:CREATE TABLE `TEST_TB_GRADE` (`ID` int(10) NOT NULL AUTO_INCREMENT,`USER_NAME` varchar(20) DEFAULT NULL,`COURSE` varchar(20) DEFAULT...
  • 数据库之行转列与列转行方法

    万次阅读 2018-06-04 23:51:28
    --创建纵表CREATE TABLE TABLE_A ( NAME VARCHAR2(20), SUBJECT VARCHAR2(20), SCORE NUMBER(22,0));
  • 结果数据库 通过FOR XML PATH把skill按-拼接在一起(-缠绕-寄生-蛛网束缚),然后使用STUFF函数把第一个-符号去掉。 SQL Code select A.* into #result from ( select '唐三' as name,'缠绕' as skill ...
  • 行转列,列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现,也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。 用传统的方法,比较好理解。层次清晰,而且比较习惯。 但是PIVOT 、...
  • 目录结构如下:行转列列转行[一]、行转列1.1、初始测试数据表结构:TEST_TB_GRADEcreatetableTEST_TB_GRADE(IDNUMBER(10)notnull,USER_NAMEVARCHAR2(20CHAR),COURSEVARCHAR2(20CHAR),SCOREFLOAT)createtableTEST_TB_...
  • 列转行:利用max(case when then) max—聚合函数 取最大值 (case course when ‘语文’ then score else 0 end) —判断 as 语文—别名作为列名 SELECT `name`, MAX( CASE WHEN course='\u8bed\u6587' THEN ...
  • oracle列转行

    2014-12-08 16:03:43
    oracle列转行的方法,有些时候页面显示要通过后台的列转行去实现,我们可以直接从sql中解决后台很复杂的问题
  • 神通数据库列转行函数和行转列函数是什么?需要怎么才可以展示出这种效果
  • 其实列转行是比较简单的用sum和decode函数就可以了,但是我遇到的项目,不知道数据谁设计的居然字段里面还设计成long类型,此long类型与java中的不同,oracle中的long是指可变长二进制数据,最长2G,哎没办法只好...
  • 作者:曹理鹏@iCocos 1、对查询进行优化,应尽可能避免全表扫描首先应考虑在 where 及 order by 涉及的上建立索引。下面我们来以一个表中177条数据比较一下,全表扫描与建立索引之后性能的一个比较.从这两种方式查询...
  • 如下所示: SELECT count(DISTINCT(a.rect_id)) zcount, a.job_dept,  DATE_FORMAT(submit_date, '%Y-%m') zsubmit_date  FROM  表名 a  WHERE  a.statu = 3  AND a.rstatu = 2  AND a.job_dept IN ('19', '...

空空如也

空空如也

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

数据库查询列转行