【转】纵表、横表互转的SQL

2024-07-14 08:34| 来源: 网络整理| 查看: 265

方法二：--用case when 实现SELECT T.NAME, SUM(CASE T.Course WHEN '语文' THEN T.Score ELSE 0 END) 语文, SUM(CASE T.Course WHEN '数学' THEN T.Score ELSE 0 END) 数学, SUM(CASE T.Course WHEN '英语' THEN T.Score ELSE 0 END) 英语 FROM T_T_STUDENT TGROUP BY T.NAME 输出结果如下：姓名语文数学英语1 刘备 90 94 922 关羽 97 53 953 张飞 80 87 68 区别如果条件是单一值时，用decode比较简便，如果判断条件比较复杂是用case when实现 -------------------- 普通行列转换假设有张学生成绩表(tb)如下:Name Subject Result张三语文　　74张三数学　　83张三物理　　93李四语文　　74李四数学　　84李四物理　　94*/

-------------------------------------------------------------------------/*想变成姓名语文数学物理 ---------- ----------- ----------- ----------- 李四 74 84 94张三 74 83 93*/

create table tb( Name varchar(10) , Subject varchar(10) , Result int)

insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)go

--静态SQL,指subject只有语文、数学、物理这三门课程。select name 姓名, max(case subject when '语文' then result else 0 end) 语文, max(case subject when '数学' then result else 0 end) 数学, max(case subject when '物理' then result else 0 end) 物理from tbgroup by name/*姓名语文数学物理 ---------- ----------- ----------- ----------- 李四 74 84 94张三 74 83 93*/

--动态SQL,指subject不止语文、数学、物理这三门课程。declare @sql varchar(8000)set @sql = 'select Name as ' + '姓名'select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'from (select distinct Subject from tb) as aset @sql = @sql + ' from tb group by name'exec(@sql) /*姓名数学物理语文 ---------- ----------- ----------- ----------- 李四 84 94 74张三 83 93 74*/

-------------------------------------------------------------------/*加个平均分，总分姓名语文数学物理平均分总分 ---------- ----------- ----------- ----------- -------------------- ----------- 李四 74 84 94 84.00 252张三 74 83 93 83.33 250*/

--静态SQL,指subject只有语文、数学、物理这三门课程。select name 姓名, max(case subject when '语文' then result else 0 end) 语文, max(case subject when '数学' then result else 0 end) 数学, max(case subject when '物理' then result else 0 end) 物理, cast(avg(result*1.0) as decimal(18,2)) 平均分, sum(result) 总分from tbgroup by name/*姓名语文数学物理平均分总分 ---------- ----------- ----------- ----------- -------------------- ----------- 李四 74 84 94 84.00 252张三 74 83 93 83.33 250*/

--动态SQL,指subject不止语文、数学、物理这三门课程。declare @sql1 varchar(8000)set @sql1 = 'select Name as ' + '姓名'select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'from (select distinct Subject from tb) as aset @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'exec(@sql1) /*姓名数学物理语文平均分总分 ---------- ----------- ----------- ----------- -------------------- ----------- 李四 84 94 74 84.00 252张三 83 93 74 83.33 250*/

drop table tb

------------------------------------------------------------------------------------------------------------------/*如果上述两表互相换一下：即

姓名语文数学物理张三 74　　83　　93李四 74　　84　　94

想变成 Name Subject Result ---------- ------- ----------- 李四语文 74李四数学 84李四物理 94张三语文 74张三数学 83张三物理 93*/

create table tb1( 姓名 varchar(10) , 语文 int , 数学 int , 物理 int)

insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)

--------------------------------------------------------------------/*加个平均分,总分Name Subject Result ---------- ------- -------------------- 李四语文 74.00李四数学 84.00李四物理 94.00李四平均分 84.00李四总分 252.00张三语文 74.00张三数学 83.00张三物理 93.00张三平均分 83.33张三总分 250.00*/

select * from( select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 union all select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1 union all select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1 union all select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1 union all select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1) torder by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when'总分' then 5 end

drop table tb1

-------------- 动态列转行

----------------新建测试表CREATE TABLE tmp_user_2(USER_ID NUMBER,MODE_NAME VARCHAR2(100),TYPE_ID NUmBER);

----------------第一部分测试数据INSERT INTO tmp_user_2 VALUES(1001, 'M1',1); INSERT INTO tmp_user_2 VALUES(1001, 'M2',2);INSERT INTO tmp_user_2 VALUES(1002, 'M1',3);INSERT INTO tmp_user_2 VALUES(1002, 'M2',4);INSERT INTO tmp_user_2 VALUES(1002, 'M3',5);INSERT INTO tmp_user_2 VALUES(1003, 'M1',6);COMMIT;

----------------行转列存储过程CREATE OR REPLACE PROCEDURE P_tmp_user_2 IS V_SQL VARCHAR2(2000); CURSOR CURSOR_1 IS SELECT DISTINCT T.MODE_NAME FROM tmp_user_2 T ORDER BY MODE_NAME;

BEGIN V_SQL := 'SELECT USER_ID'; FOR V_XCLCK IN CURSOR_1 LOOP V_SQL := V_SQL || ',' || 'SUM(DECODE(MODE_NAME,''' || V_XCLCK.MODE_NAME || ''',TYPE_ID,0)) AS ' || V_XCLCK.MODE_NAME; END LOOP;

V_SQL := V_SQL || ' FROM tmp_user_2 GROUP BY USER_ID'; --DBMS_OUTPUT.PUT_LINE(V_SQL); V_SQL := 'CREATE OR REPLACE VIEW tmp_user_3 AS ' || V_SQL; --DBMS_OUTPUT.PUT_LINE(V_SQL); EXECUTE IMMEDIATE V_SQL;END;

----------------执行存储过程BEGIN P_tmp_user_2; END;

----------------查看结果SELECT * FROM tmp_user_3;

----------------第二部分测试数据INSERT INTO tmp_user_2 VALUES(1003, 'M2',7); INSERT INTO tmp_user_2 VALUES(1004, 'M5',8); COMMIT;

----------------执行存储过程BEGIN P_tmp_user_2; END;----------------查看结果SELECT * FROM tmp_user_3;

---------------- 其他：一、横表和纵表横表：通常指我们平时在数据库中建立的表，是一种普通的建表方式。（主键、字段1、字段2......）如：时间、客户ID,基本通话费、漫游通话费，国内长途费、国际长途费....。纵表：一般不多见，在表结构不确定的时候，如需增加字段的情况下的一种建表方式。二、执行效率横表:后台数据库管理员操作简单，直观，清晰可见，一目了然。但若要给横表中添加一个或者多个字段，就须重建表结构。纵表:对于横表的弊端，纵表中只需要添加一条记录，就可以添加一个字段，所消耗的代价远比横表小。但是纵表的对于数据描述不是很清晰，而且会造成数据库数量很多。在查询的时候用到group等函数会大大降低执行效率。纵表的初始映射要慢一些，纵表的变更的映射可能要快一些，如果只是改变了单个字段时，毕竟横表字段比纵表要多很多。三、转换 1.在平时的开发过程中，可能会遇到字段的添加或者更好的维护和管理大数据量的表，就会涉及到纵表和横表之间的转换。 2.把不容易改动表结构的设计成横表，把容易经常改动不确定的表结构设计成纵表。举例：注：DECODE函数是ORACLE PL/SQL的功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。 sign函数：在数学和计算机运算中，其功能是取某个数的符号（正或负）：当x≥0，sign(x)=1; 当x

【本文地址】

【转】纵表、横表互转的SQL

【转】纵表、横表互转的SQL

今日新闻

推荐新闻