Oracle 横表、纵表一点记录

最新推荐文章于 2025-11-25 13:11:49 发布

原创最新推荐文章于 2025-11-25 13:11:49 发布 · 156 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

数据库专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了数据库中如何将横表转换为纵表，以及如何使用decode和casewhen函数进行数据分组和聚合查询，同时展示了如何在查询结果中加入总计项。

横表就是普通的建表方式，如一个表结构为：
主键、字段1、字段2、字段3。。。
如果变成纵表后，则表结构为：
主键、字段代码、字段值。
而字段代码则为字段1、字段2、字段3。

纵表对从数据库到内存的映射效率是有影响的，但细一点说也要一分为二：
纵表的初始映射要慢一些；
纵表的变更的映射可能要快一些，如果只是改变了单个字段时，毕竟横表字段比纵表要多很多。

横表的好处是清晰可见，一目了然，但是有一个弊端，如果现在要把这个表加一个字段，那么就必须重建表结构。对于这种情况，在纵表中只需要添加一条记录，就可以添加一个字段，所消耗的代价远比横表小，但是纵表的对于数据描述不是很清晰，而且会造成数据库数量很多，两者利弊在于此。所以，应该把不容易改动表结构的设计成横表，把容易经常改动不确定的表结构设计成纵表。

测试例子：

create table a(name varchar2(12),

math number,

englist number,chinese number);

插入两行记录：

张三 85 90 95

李四 90 85 86

转行查询语句：

SELECT flag, MAX(李四) AS 李四, MAX(张三) AS 张三 
  FROM (SELECT decode(NAME, '李四', fensu) 　as 李四, 
               decode(NAME, '张三', fensu) AS 张三, 
               flag 
          FROM (SELECT a.NAME, a.math AS fensu, 'math' AS flag 
                  FROM a 
                UNION ALL 
                SELECT a.NAME, a.englist AS fensu, 'englist' AS flag 
                  FROM a 
                UNION ALL 
                SELECT a.NAME, a.chinese AS fensu, 'chinese' AS flag FROM a) b 
         ORDER BY NAME, flag, fensu) 
GROUP BY flag

现有emp和dept表

EMP

empno number(4)

ename varchar2(10)

job varchar2(9)

mgr number(4)

hiredate date

sal number(7,2)

comm number(7,2)

deptno number(2)

DEPT

deptno number(2)

dname varchar2(14)

loc varchar2(13)

统计不同部门和工作的员工的总工资

实现横标转换为纵表

decode实现

select d.dname dname,
       sum(decode(e.job, 'CLERK', e.sal, 0)) CLERK,
       sum(decode(e.job, 'SALESMAN', e.sal, 0)) SALESMAN,
       sum(decode(e.job, 'ANALYST', e.sal, 0)) ANALYST,
       sum(decode(e.job, 'MANAGER', e.sal, 0)) MANAGER,
       sum(decode(e.job, 'PRESIDENT', e.sal, 0)) PRESIDENT
  from emp e
  join dept d
  on e.deptno = d.deptno
 group by d.dname;
 
 
case  when实现
select d.dname dname,
        sum(
          case e.job
            when 'CLERK' then e.sal
            else 0
          end
        ) CLERK,
        sum(
          case e.job
            when 'SALESMAN' then e.sal
            else 0
          end
        ) SALESMAN,
         sum(
          case e.job
            when 'PRESIDENT' then e.sal
            else 0
          end
        ) PRESIDENT,
         sum(
          case e.job
            when 'MANAGER' then e.sal
            else 0
          end
        ) MANAGER,
         sum(
          case e.job
            when 'ANALYST' then e.sal
            else 0
          end
        ) ANALYST
  from emp e
  join dept d
  on e.deptno = d.deptno
 group by d.dname;
 
 
带合计项的
select d.dname dname,
       sum(decode(e.job, 'CLERK', e.sal, 0)) CLERK,
       sum(decode(e.job, 'SALESMAN', e.sal, 0)) SALESMAN,
       sum(decode(e.job, 'ANALYST', e.sal, 0)) ANALYST,
       sum(decode(e.job, 'MANAGER', e.sal, 0)) MANAGER,
       sum(decode(e.job, 'PRESIDENT', e.sal, 0)) PRESIDENT
  from emp e
  join dept d on e.deptno = d.deptno
 group by d.dname
union
select '总计' dname,
       sum(decode(e.job, 'CLERK', e.sal, 0)) CLERK,
       sum(decode(e.job, 'SALESMAN', e.sal, 0)) SALESMAN,
       sum(decode(e.job, 'ANALYST', e.sal, 0)) ANALYST,
       sum(decode(e.job, 'MANAGER', e.sal, 0)) MANAGER,
       sum(decode(e.job, 'PRESIDENT', e.sal, 0)) PRESIDENT
  from emp e
  join dept d2 on e.deptno = d2.deptno