横表与纵表互换-Oracle

一、 横表与纵表

1.1 横表

  1. 定义:一行记录表示了一个实体记录信息
  2. 表结构类似:主键、字段1、字段2、字段3…
  3. 如下表所示:

    1
    2
    3
    ID  STNAME  CHINESE MATH    ENGLISH
    1 A 80.0 85.0 88.0
    2 B 82.0 86.0 85.0
  4. 巧记:在横表中,只需一条记录就可以记录一名学生所有科目的考试成绩。

1.2 纵表

  1. 定义:一行记录用于表示某个属性名和属性值对应关系
  2. 表结构类似:主键、字段代码(字段1、字段2、字段3)、字段值
  3. 如下表所示:

    1
    2
    3
    4
    5
    6
    7
    STNAME  CLSTYPE SCORE
    A 语文 80.0
    A 数学 85.0
    A 英语 88.0
    B 语文 82.0
    B 数学 86.0
    B 英语 85.0
  4. 巧记:在纵表中,需要3条记录才可以记录一名学生所有科目的考试成绩。

1.3 总结

  总之,横表的好处是清晰可见,一目了然,但是如果现在要把这个表加一个字段,那么就必须重建表结构。对于纵表,在纵表中只需要添加一条记录,就可以添加一个字段,所消耗的代价远比横表小,但是纵表的对于数据描述不是很清晰,而且会造成数据库数量很多。

二、横表和纵表的互相转换

2.1. 横表转换成纵表

  • 横表转换纵表
    横表转换成纵表
  • 使用union
    1
    2
    3
    select id,stname "姓名",'语文' "课程",chinese "分数" from table_sam_test2 union         
    select id,stname "姓名",'数学' "课程",math "分数" from table_sam_test2 union
    select id,stname "姓名",'英语' "课程",english "分数" from table_sam_test2;

2.2. 纵表转换成横表

  • 纵表转换横表
    纵表转换横表
  • 方法一:使用decode函数(推荐)

    1
    2
    3
    4
    5
    select stname "姓名",     
    sum(decode(clstype,'语文',score)) "语文",
    sum(decode(clstype,'数学',score)) "数学",
    sum(decode(clstype,'英语',score)) "英语"
    from table_sam_test1 group by stname;
  • 方法二:使用case语句

    1
    2
    3
    4
    5
    select stname "姓名", 
    sum(case clstype when '语文' then score end) "语文",
    sum(case clstype when '数学' then score end) "数学",
    sum(case clstype when '英语' then score end) "英语"
    from table_sam_test1 group by stname;
  • 方法三:使用子查询+union,如果有多列,sql语句就会很长(不推荐)

    1
    2
    3
    4
    5
    6
    with sql1 as
    (select stname,score chinese,0 math,0 english from table_sam_test1 where clstype='语文' union
    select stname,0 chinese,score math,0 english from table_sam_test1 where clstype='数学' union
    select stname,0 chinese,0 math,score english from table_sam_test1 where clstype='英语')
    select stname "姓名",sum(chinese) "语文",sum(math) "数学",sum(english) "英语"
    from sql1 group by stname;
评论